<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <meta name="generator" content="VuePress 2.0.0-beta.60">
    <script>
      (function() {
        const userMode = localStorage.getItem('vuepress-reco-color-scheme') || 'auto';
        const systemDarkMode = window.matchMedia && window.matchMedia('(prefers-color-scheme: dark)').matches;

        if (userMode === 'dark' || (userMode === 'auto' && systemDarkMode)) {
          document.documentElement.classList.toggle('dark', true);
        }
      })();
    </script>
    <title>Spring 分库分表 | 阿派 | Apai Blog</title><meta name="description" content="Just playing around">
    <link rel="modulepreload" href="/assets/app-8a43a0f0.js"><link rel="modulepreload" href="/assets/framework-e1bed10d.js"><link rel="modulepreload" href="/assets/ShardingJDBC.html-ce0afe07.js"><link rel="modulepreload" href="/assets/ShardingJDBC.html-d0f811b3.js"><link rel="prefetch" href="/assets/index.html-ef2f4f01.js" as="script"><link rel="prefetch" href="/assets/index.html-1013d654.js" as="script"><link rel="prefetch" href="/assets/index.html-a146dedc.js" as="script"><link rel="prefetch" href="/assets/index.html-f4b2eaea.js" as="script"><link rel="prefetch" href="/assets/index.html-e3ed352e.js" as="script"><link rel="prefetch" href="/assets/index.html-42ce2ab9.js" as="script"><link rel="prefetch" href="/assets/index.html-ec24ec9e.js" as="script"><link rel="prefetch" href="/assets/index.html-d3d8d7ce.js" as="script"><link rel="prefetch" href="/assets/index.html-9faf073b.js" as="script"><link rel="prefetch" href="/assets/index.html-9364b7dd.js" as="script"><link rel="prefetch" href="/assets/index.html-fc0fb462.js" as="script"><link rel="prefetch" href="/assets/index.html-603c6c19.js" as="script"><link rel="prefetch" href="/assets/index.html-0349deb8.js" as="script"><link rel="prefetch" href="/assets/index.html-09405a87.js" as="script"><link rel="prefetch" href="/assets/index.html-91577c59.js" as="script"><link rel="prefetch" href="/assets/index.html-552ae255.js" as="script"><link rel="prefetch" href="/assets/index.html-424369c4.js" as="script"><link rel="prefetch" href="/assets/index.html-1dfc0069.js" as="script"><link rel="prefetch" href="/assets/index.html-ec62f595.js" as="script"><link rel="prefetch" href="/assets/index.html-4621ef22.js" as="script"><link rel="prefetch" href="/assets/index.html-e189ec13.js" as="script"><link rel="prefetch" href="/assets/index.html-b424a45b.js" as="script"><link rel="prefetch" href="/assets/index.html-623952a8.js" as="script"><link rel="prefetch" href="/assets/index.html-cedcb7c8.js" as="script"><link rel="prefetch" href="/assets/index.html-07f61d2f.js" as="script"><link rel="prefetch" href="/assets/index.html-9ef01f1e.js" as="script"><link rel="prefetch" href="/assets/index.html-dfa6d19f.js" as="script"><link rel="prefetch" href="/assets/index.html-23dd0955.js" as="script"><link rel="prefetch" href="/assets/index.html-1e7a4ff3.js" as="script"><link rel="prefetch" href="/assets/index.html-e12f154a.js" as="script"><link rel="prefetch" href="/assets/index.html-30de2d02.js" as="script"><link rel="prefetch" href="/assets/index.html-6a244983.js" as="script"><link rel="prefetch" href="/assets/index.html-8900b10e.js" as="script"><link rel="prefetch" href="/assets/index.html-b3210461.js" as="script"><link rel="prefetch" href="/assets/biaoqian-fenlei-moban.html-2db9524d.js" as="script"><link rel="prefetch" href="/assets/MuBan-Apai.html-5af383c8.js" as="script"><link rel="prefetch" href="/assets/guide.html-5aaed80e.js" as="script"><link rel="prefetch" href="/assets/Lu-aboutMe.html-82ee5401.js" as="script"><link rel="prefetch" href="/assets/kaishi.html-9d8493a0.js" as="script"><link rel="prefetch" href="/assets/Hobby-SheYing.html-3ac28a1b.js" as="script"><link rel="prefetch" href="/assets/Lu-aboutMe.html-96d10c31.js" as="script"><link rel="prefetch" href="/assets/Work-MaYun.html-5e497e8c.js" as="script"><link rel="prefetch" href="/assets/Java-basics.html-4a44925e.js" as="script"><link rel="prefetch" href="/assets/JavaAPI.html-ae8a1843.js" as="script"><link rel="prefetch" href="/assets/JavaBiKeng04.html-c9e75b16.js" as="script"><link rel="prefetch" href="/assets/JavaGongNeng03.html-6ca97faf.js" as="script"><link rel="prefetch" href="/assets/JavaJinJie02.html-eaabb81d.js" as="script"><link rel="prefetch" href="/assets/JavaMyUtil.html-e2cff404.js" as="script"><link rel="prefetch" href="/assets/MyBatisPlus.html-b608a85b.js" as="script"><link rel="prefetch" href="/assets/MySql.html-4bcdc408.js" as="script"><link rel="prefetch" href="/assets/MySqlFunction.html-23affe6f.js" as="script"><link rel="prefetch" href="/assets/RabbitMQ.html-2cc19b22.js" as="script"><link rel="prefetch" href="/assets/Redis.html-f7a014e9.js" as="script"><link rel="prefetch" href="/assets/ShiWuAffair.html-33f5c0c4.js" as="script"><link rel="prefetch" href="/assets/Spring.html-628746d6.js" as="script"><link rel="prefetch" href="/assets/SpringConfig.html-49f3c621.js" as="script"><link rel="prefetch" href="/assets/SpringPeiZhi.html-ac6df88a.js" as="script"><link rel="prefetch" href="/assets/SpringSecurity.html-057633f1.js" as="script"><link rel="prefetch" href="/assets/WebSocket.html-57d6fc97.js" as="script"><link rel="prefetch" href="/assets/DockerCompose.html-98ad6b52.js" as="script"><link rel="prefetch" href="/assets/Linux.html-43351365.js" as="script"><link rel="prefetch" href="/assets/Nginx.html-f1276de6.js" as="script"><link rel="prefetch" href="/assets/RabbitMQ_COPY.html-5cdb6370.js" as="script"><link rel="prefetch" href="/assets/Redis_COPY.html-486e1d26.js" as="script"><link rel="prefetch" href="/assets/Redis_CRUD.html-47e0af0f.js" as="script"><link rel="prefetch" href="/assets/BatJiaoBen.html-d2d2666a.js" as="script"><link rel="prefetch" href="/assets/DmShengChen.html-bb069d8e.js" as="script"><link rel="prefetch" href="/assets/EasyExcel.html-b1eb613d.js" as="script"><link rel="prefetch" href="/assets/GitLu.html-eae89aa5.js" as="script"><link rel="prefetch" href="/assets/JavaKaiFa.html-4fbb86cc.js" as="script"><link rel="prefetch" href="/assets/JSR303.html-b976b418.js" as="script"><link rel="prefetch" href="/assets/MinIO.html-5e3d4702.js" as="script"><link rel="prefetch" href="/assets/Swagger.html-bb111a21.js" as="script"><link rel="prefetch" href="/assets/Vuepress.html-994cf672.js" as="script"><link rel="prefetch" href="/assets/Angular.html-3115afab.js" as="script"><link rel="prefetch" href="/assets/ElementUI.html-35bc6477.js" as="script"><link rel="prefetch" href="/assets/Html-Js.html-015f1806.js" as="script"><link rel="prefetch" href="/assets/Vue-basics.html-a64846f4.js" as="script"><link rel="prefetch" href="/assets/Vue3_group.html-f53be77f.js" as="script"><link rel="prefetch" href="/assets/Vue3_option.html-19f76b9e.js" as="script"><link rel="prefetch" href="/assets/ApifoxAPIceshi.html-19cd8153.js" as="script"><link rel="prefetch" href="/assets/PDManeryuanshujianmo.html-f6fa9d9e.js" as="script"><link rel="prefetch" href="/assets/OCRwenzishibie.html-11fc474d.js" as="script"><link rel="prefetch" href="/assets/EasyExcelbiaogeku.html-30777927.js" as="script"><link rel="prefetch" href="/assets/Javadaimashengchengqi.html-cf566ce0.js" as="script"><link rel="prefetch" href="/assets/JDKshuangbanben.html-1f259d3b.js" as="script"><link rel="prefetch" href="/assets/JSR303canshuxiaoyan.html-78322130.js" as="script"><link rel="prefetch" href="/assets/Mail  youjian.html-7e3a0f69.js" as="script"><link rel="prefetch" href="/assets/MyBatisPlusxiangjie.html-0168c152.js" as="script"><link rel="prefetch" href="/assets/MySQLzhucongfuzhi.html-7a4739e8.js" as="script"><link rel="prefetch" href="/assets/Redis-API.html-f488940c.js" as="script"><link rel="prefetch" href="/assets/SpringBootshiwu.html-c07a3864.js" as="script"><link rel="prefetch" href="/assets/SpringBootduxiefenli.html-f0993780.js" as="script"><link rel="prefetch" href="/assets/Springguolvqihelanjieqi.html-78feb321.js" as="script"><link rel="prefetch" href="/assets/WebSocket.html-caecf367.js" as="script"><link rel="prefetch" href="/assets/quanjuyichang-JSR303.html-add01eff.js" as="script"><link rel="prefetch" href="/assets/Docker.html-14746447.js" as="script"><link rel="prefetch" href="/assets/Dockeryuanchengfangwenbushu.html-7dc4f8d3.js" as="script"><link rel="prefetch" href="/assets/Nginxfanxiangdaili.html-42533685.js" as="script"><link rel="prefetch" href="/assets/Apai-SheYingOne.html-143bc574.js" as="script"><link rel="prefetch" href="/assets/Apai-Year.html-b713587f.js" as="script"><link rel="prefetch" href="/assets/My2305.html-b224c5e2.js" as="script"><link rel="prefetch" href="/assets/My2306.html-94838726.js" as="script"><link rel="prefetch" href="/assets/My2307.html-df2118c4.js" as="script"><link rel="prefetch" href="/assets/My2308.html-396c79bc.js" as="script"><link rel="prefetch" href="/assets/My2309.html-1ebdc4bb.js" as="script"><link rel="prefetch" href="/assets/My2310.html-d4ffbd04.js" as="script"><link rel="prefetch" href="/assets/My2311.html-30f23133.js" as="script"><link rel="prefetch" href="/assets/My2312.html-2da62191.js" as="script"><link rel="prefetch" href="/assets/Yi-HuBei.html-90d02bf7.js" as="script"><link rel="prefetch" href="/assets/Yi-ZhuHai.html-e202f6fb.js" as="script"><link rel="prefetch" href="/assets/Apache ECharts tubiao.html-6d4a4703.js" as="script"><link rel="prefetch" href="/assets/Vue3 zuheshi API.html-9fe2887f.js" as="script"><link rel="prefetch" href="/assets/Vue3 xuanxiangshi API.html-d584c181.js" as="script"><link rel="prefetch" href="/assets/Vue3-i18n-guojihuachajian.html-16f6e650.js" as="script"><link rel="prefetch" href="/assets/Batpichulijiaoben.html-307223b0.js" as="script"><link rel="prefetch" href="/assets/Gitgongzuoliu.html-fa6e129f.js" as="script"><link rel="prefetch" href="/assets/Typoratuchuang.html-22c57156.js" as="script"><link rel="prefetch" href="/assets/Vuepress.html-df146bdb.js" as="script"><link rel="prefetch" href="/assets/My2401.html-49f6440f.js" as="script"><link rel="prefetch" href="/assets/My2402.html-950d4e85.js" as="script"><link rel="prefetch" href="/assets/shenhai-0509.html-2007281b.js" as="script"><link rel="prefetch" href="/assets/zhuhaixing.html-17db8380.js" as="script"><link rel="prefetch" href="/assets/404.html-f9875e7b.js" as="script"><link rel="prefetch" href="/assets/index.html-40bced6f.js" as="script"><link rel="prefetch" href="/assets/index.html-3c78780e.js" as="script"><link rel="prefetch" href="/assets/index.html-ef51ba1b.js" as="script"><link rel="prefetch" href="/assets/index.html-bdec80a5.js" as="script"><link rel="prefetch" href="/assets/index.html-3b5427f8.js" as="script"><link rel="prefetch" href="/assets/index.html-cbd49540.js" as="script"><link rel="prefetch" href="/assets/index.html-b0b97c81.js" as="script"><link rel="prefetch" href="/assets/index.html-2793d245.js" as="script"><link rel="prefetch" href="/assets/index.html-71d9a5d9.js" as="script"><link rel="prefetch" href="/assets/index.html-4e7d77cd.js" as="script"><link rel="prefetch" href="/assets/index.html-aeace11a.js" as="script"><link rel="prefetch" href="/assets/index.html-c62ed802.js" as="script"><link rel="prefetch" href="/assets/index.html-e640d31f.js" as="script"><link rel="prefetch" href="/assets/index.html-48e1c605.js" as="script"><link rel="prefetch" href="/assets/index.html-385dcb56.js" as="script"><link rel="prefetch" href="/assets/index.html-1bb1a523.js" as="script"><link rel="prefetch" href="/assets/index.html-1ed80e8d.js" as="script"><link rel="prefetch" href="/assets/index.html-2c154a45.js" as="script"><link rel="prefetch" href="/assets/index.html-3e93d436.js" as="script"><link rel="prefetch" href="/assets/index.html-2a93e2c9.js" as="script"><link rel="prefetch" href="/assets/index.html-cff07ba3.js" as="script"><link rel="prefetch" href="/assets/index.html-7ec32a34.js" as="script"><link rel="prefetch" href="/assets/index.html-ddb40108.js" as="script"><link rel="prefetch" href="/assets/index.html-40ab6c4f.js" as="script"><link rel="prefetch" href="/assets/index.html-47e4ee8b.js" as="script"><link rel="prefetch" href="/assets/index.html-c15250e1.js" as="script"><link rel="prefetch" href="/assets/index.html-fb2ea2e4.js" as="script"><link rel="prefetch" href="/assets/index.html-d7e5408a.js" as="script"><link rel="prefetch" href="/assets/index.html-a38055d0.js" as="script"><link rel="prefetch" href="/assets/index.html-be4e50cb.js" as="script"><link rel="prefetch" href="/assets/index.html-660e4bef.js" as="script"><link rel="prefetch" href="/assets/index.html-98aa61ea.js" as="script"><link rel="prefetch" href="/assets/index.html-a6b7472f.js" as="script"><link rel="prefetch" href="/assets/index.html-ab78917a.js" as="script"><link rel="prefetch" href="/assets/biaoqian-fenlei-moban.html-f2c6c309.js" as="script"><link rel="prefetch" href="/assets/MuBan-Apai.html-5714876d.js" as="script"><link rel="prefetch" href="/assets/guide.html-9a89b22b.js" as="script"><link rel="prefetch" href="/assets/Lu-aboutMe.html-f8203e0a.js" as="script"><link rel="prefetch" href="/assets/kaishi.html-4ff9d0f3.js" as="script"><link rel="prefetch" href="/assets/Hobby-SheYing.html-e1ab7a00.js" as="script"><link rel="prefetch" href="/assets/Lu-aboutMe.html-4b735204.js" as="script"><link rel="prefetch" href="/assets/Work-MaYun.html-a185f330.js" as="script"><link rel="prefetch" href="/assets/Java-basics.html-c0d3a063.js" as="script"><link rel="prefetch" href="/assets/JavaAPI.html-f75cbb54.js" as="script"><link rel="prefetch" href="/assets/JavaBiKeng04.html-d2fc755d.js" as="script"><link rel="prefetch" href="/assets/JavaGongNeng03.html-c51a3060.js" as="script"><link rel="prefetch" href="/assets/JavaJinJie02.html-e5a66660.js" as="script"><link rel="prefetch" href="/assets/JavaMyUtil.html-6e16ee98.js" as="script"><link rel="prefetch" href="/assets/MyBatisPlus.html-1cafeb7e.js" as="script"><link rel="prefetch" href="/assets/MySql.html-5adc8c86.js" as="script"><link rel="prefetch" href="/assets/MySqlFunction.html-2c432c8c.js" as="script"><link rel="prefetch" href="/assets/RabbitMQ.html-d771200f.js" as="script"><link rel="prefetch" href="/assets/Redis.html-aa5c160a.js" as="script"><link rel="prefetch" href="/assets/ShiWuAffair.html-ce7fc825.js" as="script"><link rel="prefetch" href="/assets/Spring.html-3d622ed7.js" as="script"><link rel="prefetch" href="/assets/SpringConfig.html-6fe667f9.js" as="script"><link rel="prefetch" href="/assets/SpringPeiZhi.html-96b777da.js" as="script"><link rel="prefetch" href="/assets/SpringSecurity.html-fe792e2f.js" as="script"><link rel="prefetch" href="/assets/WebSocket.html-7fd7be86.js" as="script"><link rel="prefetch" href="/assets/DockerCompose.html-f6ef44c4.js" as="script"><link rel="prefetch" href="/assets/Linux.html-a1058183.js" as="script"><link rel="prefetch" href="/assets/Nginx.html-bf694d87.js" as="script"><link rel="prefetch" href="/assets/RabbitMQ_COPY.html-1e53d902.js" as="script"><link rel="prefetch" href="/assets/Redis_COPY.html-ef853ef3.js" as="script"><link rel="prefetch" href="/assets/Redis_CRUD.html-7a4bb7d5.js" as="script"><link rel="prefetch" href="/assets/BatJiaoBen.html-20ec59d7.js" as="script"><link rel="prefetch" href="/assets/DmShengChen.html-92a2db1f.js" as="script"><link rel="prefetch" href="/assets/EasyExcel.html-ac2c3493.js" as="script"><link rel="prefetch" href="/assets/GitLu.html-27823973.js" as="script"><link rel="prefetch" href="/assets/JavaKaiFa.html-ff11bb84.js" as="script"><link rel="prefetch" href="/assets/JSR303.html-0c745945.js" as="script"><link rel="prefetch" href="/assets/MinIO.html-958125dd.js" as="script"><link rel="prefetch" href="/assets/Swagger.html-9c9fb3be.js" as="script"><link rel="prefetch" href="/assets/Vuepress.html-4e07419a.js" as="script"><link rel="prefetch" href="/assets/Angular.html-3e716bd5.js" as="script"><link rel="prefetch" href="/assets/ElementUI.html-70835a6c.js" as="script"><link rel="prefetch" href="/assets/Html-Js.html-848bb521.js" as="script"><link rel="prefetch" href="/assets/Vue-basics.html-772005d8.js" as="script"><link rel="prefetch" href="/assets/Vue3_group.html-69369e84.js" as="script"><link rel="prefetch" href="/assets/Vue3_option.html-37ac05d4.js" as="script"><link rel="prefetch" href="/assets/ApifoxAPIceshi.html-0bcb1468.js" as="script"><link rel="prefetch" href="/assets/PDManeryuanshujianmo.html-4c377e02.js" as="script"><link rel="prefetch" href="/assets/OCRwenzishibie.html-b4248616.js" as="script"><link rel="prefetch" href="/assets/EasyExcelbiaogeku.html-511b0f0b.js" as="script"><link rel="prefetch" href="/assets/Javadaimashengchengqi.html-33991c95.js" as="script"><link rel="prefetch" href="/assets/JDKshuangbanben.html-8bba14d4.js" as="script"><link rel="prefetch" href="/assets/JSR303canshuxiaoyan.html-7d12c0ef.js" as="script"><link rel="prefetch" href="/assets/Mail  youjian.html-d520132f.js" as="script"><link rel="prefetch" href="/assets/MyBatisPlusxiangjie.html-94d1940a.js" as="script"><link rel="prefetch" href="/assets/MySQLzhucongfuzhi.html-bf156f04.js" as="script"><link rel="prefetch" href="/assets/Redis-API.html-534cd405.js" as="script"><link rel="prefetch" href="/assets/SpringBootshiwu.html-a83407ed.js" as="script"><link rel="prefetch" href="/assets/SpringBootduxiefenli.html-e50eba32.js" as="script"><link rel="prefetch" href="/assets/Springguolvqihelanjieqi.html-b7cdfd68.js" as="script"><link rel="prefetch" href="/assets/WebSocket.html-7c2b5253.js" as="script"><link rel="prefetch" href="/assets/quanjuyichang-JSR303.html-1f70d8b7.js" as="script"><link rel="prefetch" href="/assets/Docker.html-51026828.js" as="script"><link rel="prefetch" href="/assets/Dockeryuanchengfangwenbushu.html-f42be316.js" as="script"><link rel="prefetch" href="/assets/Nginxfanxiangdaili.html-a98dc972.js" as="script"><link rel="prefetch" href="/assets/Apai-SheYingOne.html-b5b7ae7c.js" as="script"><link rel="prefetch" href="/assets/Apai-Year.html-ce260a3f.js" as="script"><link rel="prefetch" href="/assets/My2305.html-8daa1c69.js" as="script"><link rel="prefetch" href="/assets/My2306.html-893e00c1.js" as="script"><link rel="prefetch" href="/assets/My2307.html-e78450ff.js" as="script"><link rel="prefetch" href="/assets/My2308.html-0e38205b.js" as="script"><link rel="prefetch" href="/assets/My2309.html-06a48ebc.js" as="script"><link rel="prefetch" href="/assets/My2310.html-59afa2c3.js" as="script"><link rel="prefetch" href="/assets/My2311.html-b634eab1.js" as="script"><link rel="prefetch" href="/assets/My2312.html-aafaa08a.js" as="script"><link rel="prefetch" href="/assets/Yi-HuBei.html-61c8ef5d.js" as="script"><link rel="prefetch" href="/assets/Yi-ZhuHai.html-26a46f60.js" as="script"><link rel="prefetch" href="/assets/Apache ECharts tubiao.html-e9cc0012.js" as="script"><link rel="prefetch" href="/assets/Vue3 zuheshi API.html-11ae0fbc.js" as="script"><link rel="prefetch" href="/assets/Vue3 xuanxiangshi API.html-ce744458.js" as="script"><link rel="prefetch" href="/assets/Vue3-i18n-guojihuachajian.html-1fa338e8.js" as="script"><link rel="prefetch" href="/assets/Batpichulijiaoben.html-06ab27bc.js" as="script"><link rel="prefetch" href="/assets/Gitgongzuoliu.html-b1e45d5c.js" as="script"><link rel="prefetch" href="/assets/Typoratuchuang.html-cb5c9b25.js" as="script"><link rel="prefetch" href="/assets/Vuepress.html-c36453a2.js" as="script"><link rel="prefetch" href="/assets/My2401.html-9b16cb88.js" as="script"><link rel="prefetch" href="/assets/My2402.html-3ade37a1.js" as="script"><link rel="prefetch" href="/assets/shenhai-0509.html-648d7d95.js" as="script"><link rel="prefetch" href="/assets/zhuhaixing.html-7d486783.js" as="script"><link rel="prefetch" href="/assets/404.html-7c90b0cc.js" as="script"><link rel="prefetch" href="/assets/reco-valine-a0c1af1f.js" as="script">
    <link rel="preload" href="/assets/style-8a0f09c6.css" as="style"><link rel="stylesheet" href="/assets/style-8a0f09c6.css">
  </head>
  <body>
    <div id="app"><!--[--><div class="theme-container"><div class="common-wrapper show-series show-catalog"><div><header class="navbar-container"><!--[--><div class="site-brand nav-item"><img class="logo" src="/logo.png" alt="阿派 | Apai Blog"><a href="/" class="site-name can-hide">阿派 | Apai Blog</a></div><div class="nav-item navbar-links-wrapper" style=""><form class="search-box" role="search"><input type="search" autocomplete="off" spellcheck="false" value><!----></form><nav class="navbar-links"><!--[--><div class="navbar-links__item"><a href="/" class="link router-link-active" aria-label="Home"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Home<!--]--></span></span><!--[--><!--]--></a></div><div class="navbar-links__item"><a href="/categories/Java/1/" class="link" aria-label="分类组"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->分类组<!--]--></span></span><!--[--><!--]--></a></div><div class="navbar-links__item"><a href="/tags/Rests/1/" class="link" aria-label="标签组"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->标签组<!--]--></span></span><!--[--><!--]--></a></div><div class="navbar-links__item"><div class="dropdown-link"><button class="dropdown-link__title" type="button" aria-label="文档组"><span class="xicon-container left title"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->文档组<!--]--></span></span><span class="arrow down"></span></button><button class="dropdown-link--mobile__title" type="button" aria-label="文档组"><span class="title"><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->文档组<!--]--></span></span></span><span class="right arrow"></span></button><ul style="display:none;" class="dropdown-link__container"><!--[--><li class="dropdown-link__item"><a href="/docs/Web-develop/Html-Js" class="link" aria-label="Web-开发"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Web-开发<!--]--></span></span><!--[--><!--]--></a></li><li class="dropdown-link__item"><a href="/docs/Java-develop/JavaMyUtil" class="link" aria-label="Java-开发"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Java-开发<!--]--></span></span><!--[--><!--]--></a></li><li class="dropdown-link__item"><a href="/docs/Linux-develop/Linux" class="link" aria-label="Linux-系统"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Linux-系统<!--]--></span></span><!--[--><!--]--></a></li><li class="dropdown-link__item"><a href="/docs/Rests-docs/Vuepress" class="link" aria-label="Apai-其他"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Apai-其他<!--]--></span></span><!--[--><!--]--></a></li><!--]--></ul></div></div><div class="navbar-links__item"><div class="dropdown-link"><button class="dropdown-link__title" type="button" aria-label="花圃里"><span class="xicon-container left title"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->花圃里<!--]--></span></span><span class="arrow down"></span></button><button class="dropdown-link--mobile__title" type="button" aria-label="花圃里"><span class="title"><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->花圃里<!--]--></span></span></span><span class="right arrow"></span></button><ul style="display:none;" class="dropdown-link__container"><!--[--><li class="dropdown-link__item"><a href="/docs/Apai-MyRecord/My-2023/My2307" class="link" aria-label="2023-花开万里"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->2023-花开万里<!--]--></span></span><!--[--><!--]--></a></li><li class="dropdown-link__item"><a href="/docs/Apai-MyRecord/My-2024/My2401" class="link" aria-label="2024-随心而来"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->2024-随心而来<!--]--></span></span><!--[--><!--]--></a></li><!--]--></ul></div></div><div class="navbar-links__item"><a href="/docs/Apai-AboutMe/Lu-aboutMe" class="link" aria-label="关于我"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->关于我<!--]--></span></span><!--[--><!--]--></a></div><!--]--></nav><span class="xicon-container btn-toggle-dark-mode btn--dark-mode"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 32 32" style="width:20px;height:20px;font-size:20px;color:inherit;"><path d="M15 2h2v3h-2z" fill="currentColor"></path><path d="M27 15h3v2h-3z" fill="currentColor"></path><path d="M15 27h2v3h-2z" fill="currentColor"></path><path d="M2 15h3v2H2z" fill="currentColor"></path><path d="M5.45 6.884l1.414-1.415l2.121 2.122l-1.414 1.414z" fill="currentColor"></path><path d="M23 7.58l2.121-2.12l1.414 1.414l-2.121 2.121z" fill="currentColor"></path><path d="M23.002 24.416l1.415-1.414l2.12 2.122l-1.413 1.414z" fill="currentColor"></path><path d="M5.47 25.13L7.59 23L9 24.42l-2.12 2.12l-1.41-1.41z" fill="currentColor"></path><path d="M16 8a8 8 0 1 0 8 8a8 8 0 0 0-8-8zm0 14a6 6 0 0 1 0-12z" fill="currentColor"></path></svg></span><span class="xicon-container btn-toggle-menus"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 32 32" style="width:20px;height:20px;font-size:20px;color:inherit;"><circle cx="16" cy="8" r="2" fill="currentColor"></circle><circle cx="16" cy="16" r="2" fill="currentColor"></circle><circle cx="16" cy="24" r="2" fill="currentColor"></circle></svg></span></div><!--]--></header><div class="mobile-menus-container"><nav class="navbar-links mobile"><!--[--><div class="navbar-links__item"><a href="/" class="link router-link-active" aria-label="Home"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Home<!--]--></span></span><!--[--><!--]--></a></div><div class="navbar-links__item"><a href="/categories/Java/1/" class="link" aria-label="分类组"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->分类组<!--]--></span></span><!--[--><!--]--></a></div><div class="navbar-links__item"><a href="/tags/Rests/1/" class="link" aria-label="标签组"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->标签组<!--]--></span></span><!--[--><!--]--></a></div><div class="navbar-links__item"><div class="dropdown-link"><button class="dropdown-link__title" type="button" aria-label="文档组"><span class="xicon-container left title"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->文档组<!--]--></span></span><span class="arrow down"></span></button><button class="dropdown-link--mobile__title" type="button" aria-label="文档组"><span class="title"><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->文档组<!--]--></span></span></span><span class="right arrow"></span></button><ul style="display:none;" class="dropdown-link__container"><!--[--><li class="dropdown-link__item"><a href="/docs/Web-develop/Html-Js" class="link" aria-label="Web-开发"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Web-开发<!--]--></span></span><!--[--><!--]--></a></li><li class="dropdown-link__item"><a href="/docs/Java-develop/JavaMyUtil" class="link" aria-label="Java-开发"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Java-开发<!--]--></span></span><!--[--><!--]--></a></li><li class="dropdown-link__item"><a href="/docs/Linux-develop/Linux" class="link" aria-label="Linux-系统"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Linux-系统<!--]--></span></span><!--[--><!--]--></a></li><li class="dropdown-link__item"><a href="/docs/Rests-docs/Vuepress" class="link" aria-label="Apai-其他"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Apai-其他<!--]--></span></span><!--[--><!--]--></a></li><!--]--></ul></div></div><div class="navbar-links__item"><div class="dropdown-link"><button class="dropdown-link__title" type="button" aria-label="花圃里"><span class="xicon-container left title"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->花圃里<!--]--></span></span><span class="arrow down"></span></button><button class="dropdown-link--mobile__title" type="button" aria-label="花圃里"><span class="title"><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->花圃里<!--]--></span></span></span><span class="right arrow"></span></button><ul style="display:none;" class="dropdown-link__container"><!--[--><li class="dropdown-link__item"><a href="/docs/Apai-MyRecord/My-2023/My2307" class="link" aria-label="2023-花开万里"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->2023-花开万里<!--]--></span></span><!--[--><!--]--></a></li><li class="dropdown-link__item"><a href="/docs/Apai-MyRecord/My-2024/My2401" class="link" aria-label="2024-随心而来"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->2024-随心而来<!--]--></span></span><!--[--><!--]--></a></li><!--]--></ul></div></div><div class="navbar-links__item"><a href="/docs/Apai-AboutMe/Lu-aboutMe" class="link" aria-label="关于我"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->关于我<!--]--></span></span><!--[--><!--]--></a></div><!--]--></nav><div class="appearance"><span>Appearance</span><span class="xicon-container btn-toggle-dark-mode"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 32 32" style="width:20px;height:20px;font-size:20px;color:inherit;"><path d="M15 2h2v3h-2z" fill="currentColor"></path><path d="M27 15h3v2h-3z" fill="currentColor"></path><path d="M15 27h2v3h-2z" fill="currentColor"></path><path d="M2 15h3v2H2z" fill="currentColor"></path><path d="M5.45 6.884l1.414-1.415l2.121 2.122l-1.414 1.414z" fill="currentColor"></path><path d="M23 7.58l2.121-2.12l1.414 1.414l-2.121 2.121z" fill="currentColor"></path><path d="M23.002 24.416l1.415-1.414l2.12 2.122l-1.413 1.414z" fill="currentColor"></path><path d="M5.47 25.13L7.59 23L9 24.42l-2.12 2.12l-1.41-1.41z" fill="currentColor"></path><path d="M16 8a8 8 0 1 0 8 8a8 8 0 0 0-8-8zm0 14a6 6 0 0 1 0-12z" fill="currentColor"></path></svg></span></div></div><div class="series-mask"></div><aside class="series-container"><div class="site-brand"><img class="logo" src="/logo.png" alt="阿派 | Apai Blog"><a href="/" class="site-name can-hide">阿派 | Apai Blog</a></div><!--[--><!--[--><section class="series-group series-item"><h5 class="series-heading">My Apai</h5><ul><li><!--[--><a href="/docs/Rests-docs/JavaKaiFa.html" class="link series-item" aria-label="Java 开发的工具"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Java 开发的工具<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Rests-docs/DmShengChen.html" class="link series-item" aria-label="Java 代码生成器"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Java 代码生成器<!--]--></span></span><!--[--><!--]--></a><!--]--></li></ul></section><!--]--><!--[--><section class="series-group series-item"><h5 class="series-heading">Apai 大杂烩</h5><ul><li><!--[--><a href="/docs/Rests-docs/Vuepress.html" class="link series-item" aria-label="个人网站搭建"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->个人网站搭建<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Rests-docs/GitLu.html" class="link series-item" aria-label="Git 工作流"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Git 工作流<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Rests-docs/BatJiaoBen.html" class="link series-item" aria-label="Bat 批处理脚本"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Bat 批处理脚本<!--]--></span></span><!--[--><!--]--></a><!--]--></li></ul></section><!--]--><!--[--><section class="series-group series-item"><h5 class="series-heading active">技术组</h5><ul><li><!--[--><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html" class="router-link-active router-link-exact-active link router-link-active series-item active" aria-label="Spring 分库分表"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Spring 分库分表<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Rests-docs/Swagger.html" class="link series-item" aria-label="Swagger 接口测试"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Swagger 接口测试<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Rests-docs/EasyExcel.html" class="link series-item" aria-label="Easy Excel 表格库"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Easy Excel 表格库<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Rests-docs/JSR303.html" class="link series-item" aria-label="JSR303n 参数校验"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->JSR303n 参数校验<!--]--></span></span><!--[--><!--]--></a><!--]--></li></ul></section><!--]--><!--[--><section class="series-group series-item"><h5 class="series-heading">工具组</h5><ul><li><!--[--><a href="/docs/Rests-docs/MinIO.html" class="link series-item" aria-label="MinIO 对象存储"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->MinIO 对象存储<!--]--></span></span><!--[--><!--]--></a><!--]--></li></ul></section><!--]--><!--]--></aside><!--[--><main class="page-container"><h1 class="page-title">Spring 分库分表</h1><div class="page-info"><span class="xicon-container left"><!--[--><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 32 32" class="xicon-icon" style="width:18px;height:18px;font-size:18px;color:inherit;"><path d="M16 4a5 5 0 1 1-5 5a5 5 0 0 1 5-5m0-2a7 7 0 1 0 7 7a7 7 0 0 0-7-7z" fill="currentColor"></path><path d="M26 30h-2v-5a5 5 0 0 0-5-5h-6a5 5 0 0 0-5 5v5H6v-5a7 7 0 0 1 7-7h6a7 7 0 0 1 7 7z" fill="currentColor"></path></svg><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->LuisApai<!--]--></span></span><span class="xicon-container left"><!--[--><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 32 32" class="xicon-icon" style="width:18px;height:18px;font-size:18px;color:inherit;"><path d="M26 4h-4V2h-2v2h-8V2h-2v2H6c-1.1 0-2 .9-2 2v20c0 1.1.9 2 2 2h20c1.1 0 2-.9 2-2V6c0-1.1-.9-2-2-2zm0 22H6V12h20v14zm0-16H6V6h4v2h2V6h8v2h2V6h4v4z" fill="currentColor"></path></svg><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->2023/04/28<!--]--></span></span><!----><!----><!----></div><div class="theme-reco-default-content"><div><h1 id="sharding-jdbc" tabindex="-1"><a class="header-anchor" href="#sharding-jdbc" aria-hidden="true">#</a> Sharding_JDBC</h1><h1 id="分库分表" tabindex="-1"><a class="header-anchor" href="#分库分表" aria-hidden="true">#</a> 分库分表</h1><p>分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库组成，将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。</p><h2 id="分库分表的方式" tabindex="-1"><a class="header-anchor" href="#分库分表的方式" aria-hidden="true">#</a> 分库分表的方式</h2><blockquote><p>分库分表中通常包括：垂直分库、水平分库、垂直分表、水平分表四种方式。</p></blockquote><h3 id="垂直分表" tabindex="-1"><a class="header-anchor" href="#垂直分表" aria-hidden="true">#</a> 垂直分表</h3><blockquote><p><strong>垂直分表定义：将一个表按照字段分成多表，每个表存储其中一部分字段</strong></p></blockquote><p>它带来的提升是：</p><p>1.为了避免IO争抢并减少锁表的几率，查看详情的用户与商品信息浏览互不影响</p><p>2.充分发挥热门数据的操作效率，商品信息的操作的高效率不会被商品描述的低效率所拖累。</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 一般来说，某业务实体中的各个数据项的访问频次是不一样的，部分数据项可能是占用存储空间比较大的BLOB或是TEXT。例如上例中的商品描述。所以，当表数据量很大时，可以将表按字段切开，将热门字段、冷门字段分开放，这些库可以放在不同的存储设备上，避免IO争抢。垂直切分带来的性能提升主要集中在热门数据的  操作效率上，而且磁盘争用情况减少。</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>通常我们按以下原则进行垂直拆分:</p><ul><li>把不常用的字段单独放在一张表;</li><li>把text，blob等大字段拆分出来放在附表中;</li><li>经常组合查询的列放在一张表中;</li></ul><h3 id="垂直分库" tabindex="-1"><a class="header-anchor" href="#垂直分库" aria-hidden="true">#</a> 垂直分库</h3><blockquote><p><strong>垂直分库是指按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，它的核心理念是专库专用。</strong></p></blockquote><p>通过垂直分表性能得到了一定程度的提升，但是还没有达到要求，并且磁盘空间也快不够了，因为数据还是始终限制在一台服务器，库内垂直分表只解决了单一表数据量过大的问题，但没有将表分布到不同的服务器上，因此每个 表还是竞争同一个物理机的CPU、内存、网络IO、磁盘</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code>它带来的提升是：
<span class="token comment">-- 解决业务层面的耦合，业务清晰</span>
<span class="token comment">-- 能对不同业务的数据进行分级管理、维护、监控、扩展等</span>
<span class="token comment">-- 高并发场景下，垂直分库一定程度的提升IO、数据库连接数、降低单机硬件资源的瓶颈</span>
垂直分库通过将表按业务分类，然后分布在不同数据库，并且可以将这些数据库部署在不同服务器上，从而达到多个服务器共同分摊压力的效果，但是依然没有解决单表数据量过大的问题
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="水平分库" tabindex="-1"><a class="header-anchor" href="#水平分库" aria-hidden="true">#</a> 水平分库</h3><blockquote><p><strong>水平分库是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上</strong>。</p></blockquote><p>它带来的提升是：</p><p>1.解决了单库大数据，高并发的性能瓶颈。</p><p>2.提高了系统的稳定性及可用性</p><div class="language-java line-numbers-mode" data-ext="java"><pre class="language-java"><code><span class="token comment">// 当一个应用难以再细粒度的垂直切分，或切分后数据量行数巨大，存在单库读写、存储性能瓶颈，这时候就需要进行水平分库了，经过水平切分的优化，往往能解决单库存储量及性能瓶颈。但由于同一个表被分配在不同的数据库，需要额外进行数据操作的路由工作，因此大大提升了系统复杂度</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><h3 id="水平分表" tabindex="-1"><a class="header-anchor" href="#水平分表" aria-hidden="true">#</a> 水平分表</h3><blockquote><p><strong>水平分表是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中</strong>。</p></blockquote><p>它带来的提升是：</p><ul><li>优化单一表数据量过大而产生的性能问题</li><li>避免IO争抢并减少锁表的几率</li></ul><div class="language-java line-numbers-mode" data-ext="java"><pre class="language-java"><code><span class="token comment">// 库内的水平分表，解决了单一表数据量过大的问题，分出来的小表中只包含一部分数据，从而使得单个表的数据量变小，提高检索性能</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><h2 id="分库分表小结" tabindex="-1"><a class="header-anchor" href="#分库分表小结" aria-hidden="true">#</a> 分库分表小结</h2><p>本章介绍了分库分表的各种方式，它们分别是垂直分表、垂直分库、水平分库和水平分表：</p><ul><li><p>垂直分表：可以把一个宽表的字段按访问频次、是否是大字段的原则拆分为多个表，这样既能使业务清晰，还能提升部分性能。拆分后，尽量从业务角度避免联查，否则性能方面将得不偿失。</p></li><li><p>垂直分库：可以把多个表按业务耦合松紧归类，分别存放在不同的库，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能，同时能提高整体架构的业务清晰度，不同的业务库可根据自身情况定制优化方案。但是它需要解决跨库带来的所有复杂问题。</p></li><li><p>水平分库：可以把一个表的数据(按数据行)分到多个不同的库，每个库只有这个表的部分数据，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能。它不仅需要解决跨库带来的所有复杂问题，还要解决数据路由的问题(数据路由问题后边介绍)。</p></li><li><p>水平分表：可以把一个表的数据(按数据行)分到多个同一个数据库的多张表中，每个表只有这个表的部分数据，这样做能小幅提升性能，它仅仅作为水平分库的一个补充优化。</p></li></ul><p><strong>一般来说，在系统设计阶段就应该根据业务耦合松紧来确定垂直分库，垂直分表方案，在数据量及访问压力不是特别大的情况，首先考虑缓存、读写分离、索引技术等方案。若数据量极大，且持续增长，再考虑水平分库水平分表方案</strong></p><h2 id="分库分表带来的问题" tabindex="-1"><a class="header-anchor" href="#分库分表带来的问题" aria-hidden="true">#</a> 分库分表带来的问题</h2><blockquote><p>分库分表能有效的缓解了单机和单库带来的性能瓶颈和压力，突破网络IO、硬件资源、连接数的瓶颈，同时也带来了一些问题</p></blockquote><h3 id="事务一致性问题" tabindex="-1"><a class="header-anchor" href="#事务一致性问题" aria-hidden="true">#</a> 事务一致性问题</h3><p>由于分库分表把数据分布在不同库甚至不同服务器，不可避免会带来<strong>分布式事务</strong>问题</p><h3 id="跨节点访问" tabindex="-1"><a class="header-anchor" href="#跨节点访问" aria-hidden="true">#</a> 跨节点访问</h3><p>在没有分库前，我们检索商品时可以通过以下SQL对店铺信息进行关联查询：</p><p>但垂直分库后**[商品信息和[店铺信息]**不在一个数据库，甚至不在一台服务器，无法进行关联查询。</p><p>可将原关联查询分为两次查询，第一次查询的结果集中找出关联数据id，然后根据id发起第二次请求得到关联数据，最后将获得到的数据进行拼装</p><h3 id="跨节点分页、排序函数" tabindex="-1"><a class="header-anchor" href="#跨节点分页、排序函数" aria-hidden="true">#</a> 跨节点分页、排序函数</h3><p>跨节点多库进行查询时，limit分页、order by排序等问题，就变得比较复杂了。需要先在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序</p><p>如，进行水平分库后的商品库，按ID倒序排序分页，取第一页以上流程是取第一页的数据，性能影响不大，但由于商品信息的分布在各数据库的数据可能是随机的，如果是取第N页，需要将所有节点前N页数据都取出来合并，再进行整体的排序，操作效率可想而知。所以请求页数越大，系统的性能也会越差。</p><p>在使用Max、Min、Sum、Count之类的函数进行计算的时候，与排序分页同理，也需要先在每个分片上执行相应的函数，然后将各个分片的结果集进行汇总和再次计算，最终将结果返回</p><h3 id="主键避重" tabindex="-1"><a class="header-anchor" href="#主键避重" aria-hidden="true">#</a> 主键避重</h3><p>在分库分表环境中，由于表中数据同时存在不同数据库中，主键值平时使用的自增长将无用武之地，某个分区数据库生成的ID无法保证全局唯一。因此需要单独设计全局主键，以避免跨库主键重复问题</p><h3 id="公共表" tabindex="-1"><a class="header-anchor" href="#公共表" aria-hidden="true">#</a> 公共表</h3><p>实际的应用场景中，参数表、数据字典表等都是数据量较小，变动少，而且属于高频联合查询的依赖表。例子中<strong>地理区域表</strong>也属于此类型。</p><p>可以将这类表在每个数据库都保存一份，所有对公共表的更新操作都同时发送到所有分库执行。</p><p>由于分库分表之后，数据被分散在不同的数据库、服务器。因此，对数据的操作也就无法通过常规方式完成，并且 它还带来了一系列的问题。好在，这些问题不是所有都需要我们在应用层面上解决，市面上有很多中间件可供我们选择，其中Sharding-JDBC使用流行度较高，我们来了解一下它</p><h1 id="sharding-jdbc-快速入门" tabindex="-1"><a class="header-anchor" href="#sharding-jdbc-快速入门" aria-hidden="true">#</a> Sharding-JDBC 快速入门</h1><h2 id="引入maven依赖" tabindex="-1"><a class="header-anchor" href="#引入maven依赖" aria-hidden="true">#</a> 引入maven依赖</h2><p>注意:</p><ul><li>这里使用 mybatis依赖 而不是 mybatis-plus依赖 [导致冲突报错}</li></ul><div class="language-xml line-numbers-mode" data-ext="xml"><pre class="language-xml"><code><span class="token comment">&lt;!-- Sharding-JDBC 分库分表 --&gt;</span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>dependency</span><span class="token punctuation">&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>groupId</span><span class="token punctuation">&gt;</span></span>org.apache.shardingsphere<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>groupId</span><span class="token punctuation">&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>artifactId</span><span class="token punctuation">&gt;</span></span>sharding-jdbc-spring-boot-starter<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>artifactId</span><span class="token punctuation">&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>version</span><span class="token punctuation">&gt;</span></span>4.0.0-RC1<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>version</span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>dependency</span><span class="token punctuation">&gt;</span></span>

<span class="token comment">&lt;!-- web 注解支持 --&gt;</span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>dependency</span><span class="token punctuation">&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>groupId</span><span class="token punctuation">&gt;</span></span>org.springframework.boot<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>groupId</span><span class="token punctuation">&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>artifactId</span><span class="token punctuation">&gt;</span></span>spring-boot-starter-web<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>artifactId</span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>dependency</span><span class="token punctuation">&gt;</span></span>

<span class="token comment">&lt;!-- mybatis --&gt;</span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>dependency</span><span class="token punctuation">&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>groupId</span><span class="token punctuation">&gt;</span></span>org.mybatis.spring.boot<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>groupId</span><span class="token punctuation">&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>artifactId</span><span class="token punctuation">&gt;</span></span>mybatis-spring-boot-starter<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>artifactId</span><span class="token punctuation">&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>version</span><span class="token punctuation">&gt;</span></span>2.1.4<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>version</span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>dependency</span><span class="token punctuation">&gt;</span></span>

<span class="token comment">&lt;!-- mysql --&gt;</span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>dependency</span><span class="token punctuation">&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>groupId</span><span class="token punctuation">&gt;</span></span>mysql<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>groupId</span><span class="token punctuation">&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>artifactId</span><span class="token punctuation">&gt;</span></span>mysql-connector-java<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>artifactId</span><span class="token punctuation">&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>scope</span><span class="token punctuation">&gt;</span></span>runtime<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>scope</span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>dependency</span><span class="token punctuation">&gt;</span></span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="分片规则配置" tabindex="-1"><a class="header-anchor" href="#分片规则配置" aria-hidden="true">#</a> 分片规则配置</h2><ul><li><p>分片规则配置是sharding-jdbc进行对分库分表操作的重要依据，</p></li><li><p>配置内容包括：数据源、主键生成策略、分片策略等</p></li><li><p>在application.properties 中配置 (使用 yal 也是可以的 只不过太长排版容易出错)</p></li></ul><div class="language-properties line-numbers-mode" data-ext="properties"><pre class="language-properties"><code><span class="token key attr-name">server.port</span><span class="token punctuation">=</span><span class="token value attr-value">8080</span>
<span class="token key attr-name">spring.application.name</span><span class="token punctuation">=</span><span class="token value attr-value">sharding-jdbc-simple</span>
<span class="token key attr-name">spring.http.charset</span><span class="token punctuation">=</span><span class="token value attr-value">UTF-8</span>
<span class="token key attr-name">spring.http.force</span><span class="token punctuation">=</span><span class="token value attr-value">true</span>

<span class="token comment"># 以下是分片规则配置</span>
<span class="token comment"># 定义数据源 可自定义 但是数据源信息都必须对应上</span>
<span class="token key attr-name">spring.shardingsphere.datasource.names</span><span class="token punctuation">=</span><span class="token value attr-value">m1</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m1.driver-class-name</span><span class="token punctuation">=</span><span class="token value attr-value">com.mysql.cj.jdbc.Driver</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m1.jdbc-url</span><span class="token punctuation">=</span><span class="token value attr-value">jdbc:mysql://localhost:3306/order_db?serverTimezone=UTC</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m1.password</span><span class="token punctuation">=</span><span class="token value attr-value">123456</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m1.type</span><span class="token punctuation">=</span><span class="token value attr-value">com.zaxxer.hikari.HikariDataSource</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m1.username</span><span class="token punctuation">=</span><span class="token value attr-value">root</span>


<span class="token comment"># 指定t_order表的分片策略，分片策略包括分片键和分片算法</span>
<span class="token comment"># t_order_$-&gt;{order_id % 2 + 1} $ = 主键 % 2 + 1 偶数储存1表 奇数储存2表</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression</span><span class="token punctuation">=</span><span class="token value attr-value">t_order_$-&gt;{order_id % 2 + 1}</span>
<span class="token comment"># order_id 表的主键列名 表策略指定列名</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column</span><span class="token punctuation">=</span><span class="token value attr-value">order_id</span>


<span class="token comment"># 指定t_order表的数据分布情况，配置数据节点 数据库策略</span>
<span class="token comment"># t_order 逻辑表名 | m1.t_order_$-&gt;{1..5} 表示 m1 数据库的 t_order_1 到 5 表(范围 5张表)</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_order.actual-data-nodes</span><span class="token punctuation">=</span><span class="token value attr-value">m1.t_order_$-&gt;{1..2}</span>


<span class="token comment"># order_id 表的主键列名 主键生成策略指定</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_order.key-generator.column</span><span class="token punctuation">=</span><span class="token value attr-value">order_id</span>
<span class="token comment"># 指定t_order表的主键生成策略为SNOWFLAKE 内置了雪花算法</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_order.key-generator.type</span><span class="token punctuation">=</span><span class="token value attr-value">SNOWFLAKE</span>


<span class="token comment"># 打开sql输出日志</span>
<span class="token key attr-name">spring.shardingsphere.props.sql.show</span><span class="token punctuation">=</span><span class="token value attr-value">true</span>
<span class="token key attr-name">logging.level.com.woniu.dao</span><span class="token punctuation">=</span><span class="token value attr-value">debug</span>
<span class="token key attr-name">logging.level.org.springframework.web</span><span class="token punctuation">=</span><span class="token value attr-value">info</span>
<span class="token key attr-name">logging.level.root</span><span class="token punctuation">=</span><span class="token value attr-value">info</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h1 id="sharding-jdbc-执行配置" tabindex="-1"><a class="header-anchor" href="#sharding-jdbc-执行配置" aria-hidden="true">#</a> Sharding-JDBC 执行配置</h1><h2 id="水平分库分表" tabindex="-1"><a class="header-anchor" href="#水平分库分表" aria-hidden="true">#</a> 水平分库分表</h2><blockquote><p><strong>分库分表配置详解</strong></p></blockquote><div class="language-java line-numbers-mode" data-ext="java"><pre class="language-java"><code><span class="token comment">// 指定数据库 m1,m2</span>
	对应的连接 所有的数据库
<span class="token comment">// 指定分库策略</span>
    指定分库策略所需的列
    写入分库策略的规则
<span class="token comment">// 指定分表策略</span>
    指定分表策略所需的列
    写入分表策略的规则
<span class="token comment">// 数据节点 指定数据库和表的数据分布</span>
<span class="token comment">// 指定 ID 主键的生成策略</span>
    指定主键的列名
    指定生成策略 内置的雪花算法
<span class="token comment">// 打开sql输出日志</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote><p>分库分表配置文件</p></blockquote><div class="language-properties line-numbers-mode" data-ext="properties"><pre class="language-properties"><code><span class="token key attr-name">server.port</span><span class="token punctuation">=</span><span class="token value attr-value">8080</span>
<span class="token key attr-name">spring.application.name</span><span class="token punctuation">=</span><span class="token value attr-value">Sharding_JDBC</span>
<span class="token key attr-name">spring.http.charset</span><span class="token punctuation">=</span><span class="token value attr-value">UTF-8</span>
<span class="token key attr-name">spring.http.force</span><span class="token punctuation">=</span><span class="token value attr-value">true</span>

<span class="token comment"># 以下是分片规则配置</span>
<span class="token comment"># 定义数据源 可自定义 但是数据源信息都必须对应上</span>
<span class="token key attr-name">spring.shardingsphere.datasource.names</span><span class="token punctuation">=</span><span class="token value attr-value">m1,m2</span>
<span class="token comment"># 分库 order_db_1</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m1.driver-class-name</span><span class="token punctuation">=</span><span class="token value attr-value">com.mysql.cj.jdbc.Driver</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m1.jdbc-url</span><span class="token punctuation">=</span><span class="token value attr-value">jdbc:mysql://localhost:3306/order_db_1?serverTimezone=UTC</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m1.password</span><span class="token punctuation">=</span><span class="token value attr-value">123456</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m1.type</span><span class="token punctuation">=</span><span class="token value attr-value">com.zaxxer.hikari.HikariDataSource</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m1.username</span><span class="token punctuation">=</span><span class="token value attr-value">root</span>
<span class="token comment"># 分库 order_db_2</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m2.driver-class-name</span><span class="token punctuation">=</span><span class="token value attr-value">com.mysql.cj.jdbc.Driver</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m2.jdbc-url</span><span class="token punctuation">=</span><span class="token value attr-value">jdbc:mysql://localhost:3306/order_db_2?serverTimezone=UTC</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m2.password</span><span class="token punctuation">=</span><span class="token value attr-value">123456</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m2.type</span><span class="token punctuation">=</span><span class="token value attr-value">com.zaxxer.hikari.HikariDataSource</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m2.username</span><span class="token punctuation">=</span><span class="token value attr-value">root</span>


<span class="token comment"># 分库策略，以user_id为分片键，分片策略为user_id % 2 + 1，user_id为偶数操作m1数据源，否则操作m2。</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_order.database‐strategy.inline.algorithm‐expression</span><span class="token punctuation">=</span> <span class="token value attr-value">m$-&gt;{user_id % 2 + 1}</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_order.database‐strategy.inline.sharding‐column</span><span class="token punctuation">=</span><span class="token value attr-value">user_id</span>


<span class="token comment"># 分表策略, 指定t_order表的分片策略，分片策略包括分片键和分片算法</span>
<span class="token comment"># t_order_$-&gt;{order_id % 2 + 1} $ = 主键 % 2 + 1 偶数储存1表 奇数储存2表</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression</span><span class="token punctuation">=</span><span class="token value attr-value">t_order_$-&gt;{order_id % 2 + 1}</span>
<span class="token comment"># order_id 表的主键列名 表策略指定列名</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column</span><span class="token punctuation">=</span><span class="token value attr-value">order_id</span>


<span class="token comment"># 指定 数据库和表 的数据分布情况，配置数据节点 数据库策略</span>
<span class="token comment"># t_order 逻辑表名 | m1.t_order_$-&gt;{1..5} 表示 m1 数据库的 t_order_1 到 5 表(范围 5张表)</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_order.actual-data-nodes</span><span class="token punctuation">=</span><span class="token value attr-value">m$-&gt;{1..2}.t_order_$-&gt;{1..2}</span>


<span class="token comment"># 指定 t_order表的主键生成策略为SNOWFLAKE 内置了雪花算法</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_order.key-generator.type</span><span class="token punctuation">=</span><span class="token value attr-value">SNOWFLAKE</span>
<span class="token comment"># order_id 表的主键列名 主键生成策略指定</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_order.key-generator.column</span><span class="token punctuation">=</span><span class="token value attr-value">order_id</span>


<span class="token comment"># 打开sql输出日志</span>
<span class="token key attr-name">spring.shardingsphere.props.sql.show</span><span class="token punctuation">=</span><span class="token value attr-value">true</span>
<span class="token key attr-name">logging.level.com.woniu.dao</span><span class="token punctuation">=</span><span class="token value attr-value">debug</span>
<span class="token key attr-name">logging.level.org.springframework.web</span><span class="token punctuation">=</span><span class="token value attr-value">info</span>
<span class="token key attr-name">logging.level.root</span><span class="token punctuation">=</span><span class="token value attr-value">info</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="垂直分库分表" tabindex="-1"><a class="header-anchor" href="#垂直分库分表" aria-hidden="true">#</a> 垂直分库分表</h2><blockquote><p><strong>分库分表配置详解</strong></p></blockquote><div class="language-java line-numbers-mode" data-ext="java"><pre class="language-java"><code><span class="token comment">// 指定数据库 m0,m1,m2</span>
	对应的连接 所有的数据库
<span class="token comment">// 指定分库策略</span>
    指定分库策略所需的列
    写入分库策略的规则
    由于垂直 t_user 没有多个数据库，故没有分库，不需要配置分库和分库的策略
<span class="token comment">// 指定分表策略</span>
    指定分表策略所需的列
    写入分表策略的规则
    垂直分库的表就算只有一个也一定要分片策略要配置
<span class="token comment">// 数据节点 指定数据库和表的数据分布 </span>
    配置 垂直分库的 t_user 数据节点
<span class="token comment">// 指定 ID 主键的生成策略</span>
    指定主键的列名
    指定生成策略 内置的雪花算法
<span class="token comment">// 打开sql输出日志</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote><p>分库分表配置文件</p></blockquote><ul><li>定义数据源 可自定义 但是数据源信息都必须对应上</li><li>由于垂直 t_user 没有多个数据库，故没有分库，不需要配置分库和分库的策略</li><li>垂直分库的表就算只有一个也一定要分片策略要配置</li></ul><div class="language-properties line-numbers-mode" data-ext="properties"><pre class="language-properties"><code><span class="token key attr-name">server.port</span><span class="token punctuation">=</span><span class="token value attr-value">8080</span>
<span class="token key attr-name">spring.application.name</span><span class="token punctuation">=</span><span class="token value attr-value">Sharding_JDBC</span>
<span class="token key attr-name">spring.http.charset</span><span class="token punctuation">=</span><span class="token value attr-value">UTF-8</span>
<span class="token key attr-name">spring.http.force</span><span class="token punctuation">=</span><span class="token value attr-value">true</span>

<span class="token comment"># 以下是分片规则配置</span>
<span class="token comment"># 定义数据源 可自定义 但是数据源信息都必须对应上</span>
<span class="token key attr-name">spring.shardingsphere.datasource.names</span><span class="token punctuation">=</span><span class="token value attr-value">m0,m1,m2</span>
<span class="token comment"># 垂直分库 user_db</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m0.driver-class-name</span><span class="token punctuation">=</span><span class="token value attr-value">com.mysql.cj.jdbc.Driver</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m0.jdbc-url</span><span class="token punctuation">=</span><span class="token value attr-value">jdbc:mysql://localhost:3306/user_db?serverTimezone=UTC</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m0.password</span><span class="token punctuation">=</span><span class="token value attr-value">root</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m0.username</span><span class="token punctuation">=</span><span class="token value attr-value">root</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m0.type</span><span class="token punctuation">=</span><span class="token value attr-value">com.zaxxer.hikari.HikariDataSource</span>
<span class="token comment"># 分库 order_db_1</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m1.driver-class-name</span><span class="token punctuation">=</span><span class="token value attr-value">com.mysql.cj.jdbc.Driver</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m1.jdbc-url</span><span class="token punctuation">=</span><span class="token value attr-value">jdbc:mysql://localhost:3306/order_db_1?serverTimezone=UTC</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m1.password</span><span class="token punctuation">=</span><span class="token value attr-value">123456</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m1.type</span><span class="token punctuation">=</span><span class="token value attr-value">com.zaxxer.hikari.HikariDataSource</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m1.username</span><span class="token punctuation">=</span><span class="token value attr-value">root</span>
<span class="token comment"># 分库 order_db_2</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m2.driver-class-name</span><span class="token punctuation">=</span><span class="token value attr-value">com.mysql.cj.jdbc.Driver</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m2.jdbc-url</span><span class="token punctuation">=</span><span class="token value attr-value">jdbc:mysql://localhost:3306/order_db_2?serverTimezone=UTC</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m2.password</span><span class="token punctuation">=</span><span class="token value attr-value">123456</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m2.type</span><span class="token punctuation">=</span><span class="token value attr-value">com.zaxxer.hikari.HikariDataSource</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m2.username</span><span class="token punctuation">=</span><span class="token value attr-value">root</span>


<span class="token comment"># 分库策略，以user_id为分片键，分片策略为user_id % 2 + 1，user_id为偶数操作m1数据源，否则操作m2。</span>
<span class="token comment"># 由于垂直 t_user 没有多个数据库，故没有分库，不需要配置分库和分库的策略</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_order.database‐strategy.inline.algorithm‐expression</span><span class="token punctuation">=</span> <span class="token value attr-value">m$-&gt;{user_id % 2 + 1}</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_order.database‐strategy.inline.sharding‐column</span><span class="token punctuation">=</span><span class="token value attr-value">user_id</span>


<span class="token comment"># 分表策略, 指定t_order表的分片策略，分片策略包括分片键和分片算法</span>
<span class="token comment"># t_order_$-&gt;{order_id % 2 + 1} $ = 主键 % 2 + 1 偶数储存1表 奇数储存2表</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression</span><span class="token punctuation">=</span><span class="token value attr-value">t_order_$-&gt;{order_id % 2 + 1}</span>
<span class="token comment"># order_id 表的主键列名 表策略指定列名</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column</span><span class="token punctuation">=</span><span class="token value attr-value">order_id</span>
<span class="token comment"># 垂直分库的表就算只有一个也一定要分片策略要配置</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression</span> <span class="token punctuation">=</span> <span class="token value attr-value">t_user</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column</span> <span class="token punctuation">=</span> <span class="token value attr-value">user_id </span>



<span class="token comment"># 指定 数据库和表 的数据分布情况，配置数据节点 数据库策略</span>
<span class="token comment"># t_order 逻辑表名 | m1.t_order_$-&gt;{1..5} 表示 m1 数据库的 t_order_1 到 5 表(范围 5张表)</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_order.actual-data-nodes</span><span class="token punctuation">=</span><span class="token value attr-value">m$-&gt;{1..2}.t_order_$-&gt;{1..2}</span>
<span class="token comment"># 配置 垂直分库的 t_user 数据节点</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_user.actual-data-nodes</span> <span class="token punctuation">=</span><span class="token value attr-value">m$-&gt;{0}.t_user </span>


<span class="token comment"># 指定 t_order表的主键生成策略为SNOWFLAKE 内置了雪花算法</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_order.key-generator.type</span><span class="token punctuation">=</span><span class="token value attr-value">SNOWFLAKE</span>
<span class="token comment"># order_id 表的主键列名 主键生成策略指定</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_order.key-generator.column</span><span class="token punctuation">=</span><span class="token value attr-value">order_id</span>


<span class="token comment"># 打开sql输出日志</span>
<span class="token key attr-name">spring.shardingsphere.props.sql.show</span><span class="token punctuation">=</span><span class="token value attr-value">true</span>
<span class="token key attr-name">logging.level.com.woniu.dao</span><span class="token punctuation">=</span><span class="token value attr-value">debug</span>
<span class="token key attr-name">logging.level.org.springframework.web</span><span class="token punctuation">=</span><span class="token value attr-value">info</span>
<span class="token key attr-name">logging.level.root</span><span class="token punctuation">=</span><span class="token value attr-value">info</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="公共表-1" tabindex="-1"><a class="header-anchor" href="#公共表-1" aria-hidden="true">#</a> 公共表</h2><p>公共表属于系统中数据量较小，变动少，而且属于高频联合查询的依赖表。参数表、数据字典表等属于此类型。可以将这类表在每个数据库都保存一份，所有更新操作都同时发送到所有分库执行。接下来看一下如何使用Sharding-JDBC实现公共表</p><blockquote><p>在Sharding-JDBC规则中修改</p></blockquote><ul><li>实现两个数据库的公共表的同时的增删改 联动</li><li>原理既是: 同时修改所有数据库的这一张表 | 一条sql换库执行多次</li></ul><div class="language-properties line-numbers-mode" data-ext="properties"><pre class="language-properties"><code><span class="token comment"># 指定t_dict为公共表</span>
<span class="token key attr-name">spring.shardingsphere.sharding.broadcast-tables</span><span class="token punctuation">=</span><span class="token value attr-value">t_dict</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><h1 id="mysql-主从同步" tabindex="-1"><a class="header-anchor" href="#mysql-主从同步" aria-hidden="true">#</a> MySql 主从同步</h1><h2 id="_1-新增mysql的实例" tabindex="-1"><a class="header-anchor" href="#_1-新增mysql的实例" aria-hidden="true">#</a> 1.新增mysql的实例</h2><blockquote><p>复制原有mysql如：C:\dev\mysql-8.0.19-winx64(作为主库) -&gt;C:\dev\mysql-8.0.19-winx64-s1(作为从库)，并修改以下从库的my.ini [第二步]</p></blockquote><p><strong>然后将从库安装为windows服务，注意配置文件位置</strong></p><ul><li>使用管理员打开 cmd 命令板</li><li>进入mysql安装的位置的 bin 目录下执行 | d: [进入D盘] | cd /D:\mysql-8.0.19-apai\bin 进入bin目录</li><li>出现 Service successfully installed. 表示成功</li></ul><div class="language-bash line-numbers-mode" data-ext="sh"><pre class="language-bash"><code>D:<span class="token punctuation">\</span>mysql-8.0.19-apai<span class="token punctuation">\</span>bin<span class="token operator">&gt;</span> mysqld <span class="token function">install</span> mysqls1 ‐‐defaults‐file<span class="token operator">=</span><span class="token string">&quot;D:\mysql-8.0.19-apai\my.ini&quot;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>由于从库是从主库复制过来的，因此里面的数据完全一致，可使用原来的账号、密码登录</p><h2 id="_2-修改主、从库的配置文件-my-ini" tabindex="-1"><a class="header-anchor" href="#_2-修改主、从库的配置文件-my-ini" aria-hidden="true">#</a> 2.修改主、从库的配置文件(my.ini)</h2><p>注意: 设置服务id时的位置 建议写在 [mysqld] 下方</p><blockquote><p>主库：</p></blockquote><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>[mysqld]
# 运行端口 主从不能一致
port=3306
# 设置服务id 主从不能一致
server-id=1
# 指定数据库安装位置
basedir=C:\mysql-8.0.19-winx64
# 指定数据库数据的存放位置
datadir=C:\mysql-8.0.19-winx64\data

max_connections=200
max_connect_errors=10
character-set-server=UTF8MB4
default-storage-engine=INNODB
default_authentication_plugin=mysql_native_password

# 开启日志
log-bin=master‐bin
# 设置需要同步的数据库
binlog-do-db=user_db 
# 屏蔽系统库同步
binlog-ignore-db=mysql
binlog-ignore-db=information_schema 
binlog-ignore-db=performance_schema


[mysql]
default-character-set=UTF8MB4


[client]
port=3306
default-character-set=utf8
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote><p>从库:</p></blockquote><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>[mysqld]
# 运行端口 主从不能一致
port=3307
#设置服务id 主从不能一致
server-id =2
# 指定数据库安装位置
basedir=D:\mysql-8.0.19-apai
# 指定数据库数据的存放位置
datadir=D:\mysql-8.0.19-apai\data

max_connections=200
max_connect_errors=10
character-set-server=UTF8MB4
default-storage-engine=INNODB
default_authentication_plugin=mysql_native_password

# 开启日志
log-bin = master-bin
# 设置需要同步的数据库
replicate_wild_do_table=user_db.%
# 屏蔽系统库同步
replicate_wild_ignore_table=mysql.% 
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%


[mysql]
default-character-set=UTF8MB4


[client]
port=3307
default-character-set=utf8
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote><p>重启主库和从库，在cmd窗口执行如下</p></blockquote><div class="language-cmd line-numbers-mode" data-ext="cmd"><pre class="language-cmd"><code>net start [主库服务名mysql]
net start [从库服务名mysqls1]
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><p><strong>注意:</strong></p><ul><li>请注意，主从MySQL下的数据(data)目录下有个文件auto.cnf，文件中定义了uuid，要保证主从数据库实例的uuid不一样，直接删除从库auto.cnf文件，每次mysql启动都会生成该文件，里面存放UUID</li></ul><div class="language-cmd line-numbers-mode" data-ext="cmd"><pre class="language-cmd"><code># 如果出现 在服务内 启动报错 原因: 指定启动的mysql路径错误则可以到注册表进行修改
# D:\mysql-8.0.19-apai\bin\mysqld mysqlsevicename
# 计算机注册表: \HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\mysql服务名
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="_3-授权主从复制专用账号" tabindex="-1"><a class="header-anchor" href="#_3-授权主从复制专用账号" aria-hidden="true">#</a> 3.授权主从复制专用账号</h2><blockquote><p>在主数据库上创建账号，将来从库连接上主库需要该账号建立和主库的连接</p></blockquote><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>CREATE USER &#39;db_sync&#39;@&#39;%&#39; IDENTIFIED WITH mysql_native_password BY &#39;123456&#39;;
grant replication slave on *.* to &#39;db_sync&#39;@&#39;%&#39;;  
FLUSH PRIVILEGES;
#‘%’表示任意客户端的ip地址
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>master连接的是主库，即端口为3306</p><p>slave连接的是从库，即端口为3307</p><h2 id="_4-确认位点" tabindex="-1"><a class="header-anchor" href="#_4-确认位点" aria-hidden="true">#</a> 4.确认位点</h2><blockquote><p>记录主库下的日志文件名以及从哪个点开始同步</p><p>在从库进行设置时需要配置该两个数值</p></blockquote><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>#flush logs 有时候同步日志文件没有刷新,需要执行flush logs;
show master status;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="_5-设置从库向主库同步数据检查链路" tabindex="-1"><a class="header-anchor" href="#_5-设置从库向主库同步数据检查链路" aria-hidden="true">#</a> 5.设置从库向主库同步数据检查链路</h2><p><strong>查看主库和从库的UUID和serverid</strong></p><blockquote><p>必须保证 主库和从库的UUID和serverid 不一致 | 否则报错</p></blockquote><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>show variables like &#39;%server_uuid%&#39;;
show variables like &#39;%server_id%&#39;;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><p><strong>进行从库向主库同步数据检查链路</strong></p><blockquote><p>需配置 主库的位点</p></blockquote><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>#注意 如果之前此备库已有主库指向 需要先执行以下命令清空
STOP SLAVE;
STOP SLAVE IO_THREAD FOR CHANNEL &#39;&#39;;
reset slave all;

#=======修改从库指向到主库，使用上一步记录的文件名以及位点============
CHANGE MASTER TO master_host = &#39;localhost&#39;,master_user = &#39;主库创建的账号&#39;,master_password = &#39;123456&#39;,master_log_file = &#39;位点 File&#39;,master_log_pos = 位点 Position;
#启动同步
START SLAVE; 
#查看从库状态Slave_IO_Runing和Slave_SQL_Runing都为Yes说明同步成功，如果不为Yes，请检查error_log列，然后排查相关异常
show slave status;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote><p>在检查状态时 两个都是 yes 即成功 | 否则失败 可在 后边的 Erro 列里查询错误信息</p></blockquote><p>最后测试在主库修改数据库，看从库是否能够同步成功。</p><blockquote><p>注意，也可以在从库执行，show master status，也就是说从库也可以是别的数据库的主库，主库也可以是别的数据库的从库，另外有时候slave_io_running为no，查看error_log列，或者重启一下从库</p></blockquote><h1 id="sharding-jdbc-读写分离" tabindex="-1"><a class="header-anchor" href="#sharding-jdbc-读写分离" aria-hidden="true">#</a> Sharding-JDBC 读写分离</h1><p><strong>配置</strong></p><ul><li>配置 读写分离 的两个数据源</li><li>分库策略 m0 写入数据 s0 读取数据 同时指定表</li><li>分表策略 读写分离 指定表和表的主键</li></ul><div class="language-properties line-numbers-mode" data-ext="properties"><pre class="language-properties"><code><span class="token key attr-name">server.port</span><span class="token punctuation">=</span><span class="token value attr-value">8080</span>
<span class="token key attr-name">spring.application.name</span><span class="token punctuation">=</span><span class="token value attr-value">Sharding_JDBC</span>
<span class="token key attr-name">spring.http.charset</span><span class="token punctuation">=</span><span class="token value attr-value">UTF-8</span>
<span class="token key attr-name">spring.http.force</span><span class="token punctuation">=</span><span class="token value attr-value">true</span>

<span class="token comment"># 以下是分片规则配置</span>
<span class="token comment"># 定义数据源 可自定义 但是数据源信息都必须对应上</span>
<span class="token key attr-name">spring.shardingsphere.datasource.names</span><span class="token punctuation">=</span><span class="token value attr-value">m0,m1,m2,s0</span>

<span class="token key attr-name">spring.shardingsphere.datasource.m1.driver-class-name</span><span class="token punctuation">=</span><span class="token value attr-value">com.mysql.cj.jdbc.Driver</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m1.jdbc-url</span><span class="token punctuation">=</span><span class="token value attr-value">jdbc:mysql://localhost:3306/order_db_1?serverTimezone=UTC</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m1.password</span><span class="token punctuation">=</span><span class="token value attr-value">123456</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m1.type</span><span class="token punctuation">=</span><span class="token value attr-value">com.zaxxer.hikari.HikariDataSource</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m1.username</span><span class="token punctuation">=</span><span class="token value attr-value">root</span>

<span class="token key attr-name">spring.shardingsphere.datasource.m2.driver-class-name</span><span class="token punctuation">=</span><span class="token value attr-value">com.mysql.cj.jdbc.Driver</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m2.jdbc-url</span><span class="token punctuation">=</span><span class="token value attr-value">jdbc:mysql://localhost:3306/order_db_2?serverTimezone=UTC</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m2.password</span><span class="token punctuation">=</span><span class="token value attr-value">123456</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m2.type</span><span class="token punctuation">=</span><span class="token value attr-value">com.zaxxer.hikari.HikariDataSource</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m2.username</span><span class="token punctuation">=</span><span class="token value attr-value">root</span>
<span class="token comment"># 配置 读写分离的数据源</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m0.driver-class-name</span><span class="token punctuation">=</span><span class="token value attr-value">com.mysql.cj.jdbc.Driver</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m0.jdbc-url</span><span class="token punctuation">=</span><span class="token value attr-value">jdbc:mysql://localhost:3306/user_db?serverTimezone=UTC</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m0.password</span><span class="token punctuation">=</span><span class="token value attr-value">123456</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m0.username</span><span class="token punctuation">=</span><span class="token value attr-value">root</span>
<span class="token key attr-name">spring.shardingsphere.datasource.m0.type</span><span class="token punctuation">=</span><span class="token value attr-value">com.zaxxer.hikari.HikariDataSource</span>
<span class="token comment"># 配置 读写分离的数据源</span>
<span class="token key attr-name">spring.shardingsphere.datasource.s0.driver-class-name</span><span class="token punctuation">=</span><span class="token value attr-value">com.mysql.cj.jdbc.Driver</span>
<span class="token key attr-name">spring.shardingsphere.datasource.s0.jdbc-url</span><span class="token punctuation">=</span><span class="token value attr-value">jdbc:mysql://localhost:3306/user_db?serverTimezone=UTC</span>
<span class="token key attr-name">spring.shardingsphere.datasource.s0.password</span><span class="token punctuation">=</span><span class="token value attr-value">123456</span>
<span class="token key attr-name">spring.shardingsphere.datasource.s0.username</span><span class="token punctuation">=</span><span class="token value attr-value">root</span>
<span class="token key attr-name">spring.shardingsphere.datasource.s0.type</span><span class="token punctuation">=</span><span class="token value attr-value">com.zaxxer.hikari.HikariDataSource</span>


<span class="token comment"># 分库策略，以user_id为分片键，分片策略为user_id % 2 + 1，user_id为偶数操作m1数据源，否则操作m2。</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_order.database?strategy.inline.algorithm?expression</span><span class="token punctuation">=</span> <span class="token value attr-value">m$-&gt;{user_id % 2 + 1}</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_order.database?strategy.inline.sharding?column</span><span class="token punctuation">=</span><span class="token value attr-value">user_id</span>
<span class="token comment"># 分库策略 m0 写入数据 s0 读取数据</span>
<span class="token key attr-name">spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name</span><span class="token punctuation">=</span><span class="token value attr-value">m0</span>
<span class="token key attr-name">spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names</span><span class="token punctuation">=</span><span class="token value attr-value">s0</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_user.actual-data-nodes</span><span class="token punctuation">=</span><span class="token value attr-value">ds0.t_user</span>


<span class="token comment"># 分表策略, 指定t_order表的分片策略，分片策略包括分片键和分片算法</span>
<span class="token comment"># t_order_$-&gt;{order_id % 2 + 1} $ = 主键 % 2 + 1 偶数储存1表 奇数储存2表</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression</span><span class="token punctuation">=</span><span class="token value attr-value">t_order_$-&gt;{order_id % 2 + 1}</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column</span><span class="token punctuation">=</span><span class="token value attr-value">order_id</span>
<span class="token comment"># 分表策略 读写分离</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression</span> <span class="token punctuation">=</span> <span class="token value attr-value">t_user</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column</span> <span class="token punctuation">=</span> <span class="token value attr-value">user_id</span>


<span class="token comment"># 指定 数据库和表 的数据分布情况，配置数据节点 数据库策略</span>
<span class="token comment"># t_order 逻辑表名 | m1.t_order_$-&gt;{1..5} 表示 m1 数据库的 t_order_1 到 5 表(范围 5张表)</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_order.actual-data-nodes</span><span class="token punctuation">=</span><span class="token value attr-value">m$-&gt;{1..2}.t_order_$-&gt;{1..2}</span>
<span class="token comment"># 指定中间表 t_dict 同时写入</span>
<span class="token key attr-name">spring.shardingsphere.sharding.broadcast-tables</span><span class="token punctuation">=</span><span class="token value attr-value">t_dict</span>

<span class="token comment"># 指定 t_order表的主键生成策略为SNOWFLAKE 内置了雪花算法</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_order.key-generator.type</span><span class="token punctuation">=</span><span class="token value attr-value">SNOWFLAKE</span>
<span class="token comment"># order_id 表的主键列名 主键生成策略指定</span>
<span class="token key attr-name">spring.shardingsphere.sharding.tables.t_order.key-generator.column</span><span class="token punctuation">=</span><span class="token value attr-value">order_id</span>


<span class="token comment"># 打开sql输出日志</span>
<span class="token key attr-name">spring.shardingsphere.props.sql.show</span><span class="token punctuation">=</span><span class="token value attr-value">true</span>
<span class="token key attr-name">logging.level.com.woniu.dao</span><span class="token punctuation">=</span><span class="token value attr-value">debug</span>
<span class="token key attr-name">logging.level.org.springframework.web</span><span class="token punctuation">=</span><span class="token value attr-value">info</span>
<span class="token key attr-name">logging.level.root</span><span class="token punctuation">=</span><span class="token value attr-value">info</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h1 id="sharding-jdbc-执行原理" tabindex="-1"><a class="header-anchor" href="#sharding-jdbc-执行原理" aria-hidden="true">#</a> Sharding-JDBC 执行原理</h1><h2 id="基本概念" tabindex="-1"><a class="header-anchor" href="#基本概念" aria-hidden="true">#</a> 基本概念</h2><blockquote><p>逻辑表</p></blockquote><p>水平拆分的数据表的总称。例：订单数据表根据主键尾数拆分为10张表，分别是t_order_0、t_order_1、t_order_2...到t_order_9,它们的逻辑表为t_order</p><blockquote><p>真实表</p></blockquote><p>在分片的数据库中真实存在的物理表。即上个示例中的t_order_0到t_order_9</p><blockquote><p>数据节点</p></blockquote><p>数据分片的最小物理单元。由数据源名称和数据表组成，例ds_0.t_order_0</p><blockquote><p>绑定表</p></blockquote><p>指分片规则一致的主表和子表。例如：t_order表和t_order_item表，均按照order_id分片,绑定表之间的分区键完全相同，则此两张表互为绑定表关系。绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升。举例说明，如果SQL为</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">SELECT</span> i<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">FROM</span> t_order o <span class="token keyword">JOIN</span> t_order_item i <span class="token keyword">ON</span> o<span class="token punctuation">.</span>order_id<span class="token operator">=</span>i<span class="token punctuation">.</span>order_id <span class="token keyword">WHERE</span> o<span class="token punctuation">.</span>order_id <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span> <span class="token number">11</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>在不配置绑定表关系时，假设分片键order_id将数值10路由至第0片，将数值11路由至第1片,那么路由后的SQL</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">SELECT</span> i<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">FROM</span> t_order_0 o <span class="token keyword">JOIN</span> t_order_item_0 i <span class="token keyword">ON</span> o<span class="token punctuation">.</span>order_id<span class="token operator">=</span>i<span class="token punctuation">.</span>order_id <span class="token keyword">WHERE</span> o<span class="token punctuation">.</span>order_id <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span> <span class="token number">11</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> i<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">FROM</span> t_order_0 o <span class="token keyword">JOIN</span> t_order_item_1 i <span class="token keyword">ON</span> o<span class="token punctuation">.</span>order_id<span class="token operator">=</span>i<span class="token punctuation">.</span>order_id <span class="token keyword">WHERE</span> o<span class="token punctuation">.</span>order_id <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span> <span class="token number">11</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> i<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">FROM</span> t_order_1 o <span class="token keyword">JOIN</span> t_order_item_0 i <span class="token keyword">ON</span> o<span class="token punctuation">.</span>order_id<span class="token operator">=</span>i<span class="token punctuation">.</span>order_id <span class="token keyword">WHERE</span> o<span class="token punctuation">.</span>order_id <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span> <span class="token number">11</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> i<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">FROM</span> t_order_1 o <span class="token keyword">JOIN</span> t_order_item_1 i <span class="token keyword">ON</span> o<span class="token punctuation">.</span>order_id<span class="token operator">=</span>i<span class="token punctuation">.</span>order_id <span class="token keyword">WHERE</span> o<span class="token punctuation">.</span>order_id <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span> <span class="token number">11</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>在配置绑定表关系后，路由的SQL应该为2条，因为我们知道t_order_0和t_order_item_0就是在同一个数据库，t_order_1和t_order_item_1在同一个数据库</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">SELECT</span> i<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">FROM</span> t_order_0 o <span class="token keyword">JOIN</span> t_order_item_0 i <span class="token keyword">ON</span> o<span class="token punctuation">.</span>order_id<span class="token operator">=</span>i<span class="token punctuation">.</span>order_id <span class="token keyword">WHERE</span> o<span class="token punctuation">.</span>order_id <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span> <span class="token number">11</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> i<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">FROM</span> t_order_1 o <span class="token keyword">JOIN</span> t_order_item_1 i <span class="token keyword">ON</span> o<span class="token punctuation">.</span>order_id<span class="token operator">=</span>i<span class="token punctuation">.</span>order_id <span class="token keyword">WHERE</span> o<span class="token punctuation">.</span>order_id <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span> <span class="token number">11</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote><p>广播表</p></blockquote><p>指所有的分片数据源中都存在的表，表结构和表中的数据在每个数据库中均完全一致。适用于数据量不大且需要与海量数据的表进行关联查询的场景，例如：字典表</p><blockquote><p>分片键</p></blockquote><p>用于分片的数据库字段，是将数据库(表)水平拆分的关键字段。例：将订单表中的订单主键的尾数取模分片，则订单主键为分片字段。 SQL中如果无分片字段，将执行全路由，性能较差。 除了对单分片字段的支持，Sharding-Jdbc也支持根据多个字段进行分片</p><blockquote><p>分片算法</p></blockquote><p>通过分片算法将数据分片，支持通过=、BETWEEN和IN分片。分片算法需要应用方开发者自行实现，可实现的灵活度非常高。包括：精确分片算法 、范围分片算法 ，复合分片算法等。例如：where order_id = ?将采用精确分片算法，where order_id in (?,?,?)将采用精确分片算法，where order_id BETWEEN ? and ? 将采用范围分片算 法，复合分片算法用于分片键有多个复杂情况</p><blockquote><p>分片策略</p></blockquote><p>分片键+分片算法=分片策略，由于分片算法的独立性，将其独立抽离。真正可用于分片操作的是分片键 + 分片算法，也就是分片策略。内置的分片策略大致可分为尾数取模、哈希、范围、标签、时间等。由用户方配置的分片策略则更加灵活，常用的使用行表达式配置分片策略，它采用Groovy表达式表示，如: 表示t_user_$-&gt;{u_id % 8}表示t_user表根据u_id模8，而分成8张表，表名称为t_user_0到t_user_7</p><p>自增主键生成策略</p><p>通过在客户端生成自增主键替换以数据库原生自增主键的方式，做到分布式主键无重复</p><h2 id="sql解析" tabindex="-1"><a class="header-anchor" href="#sql解析" aria-hidden="true">#</a> SQL解析</h2><p>当Sharding-JDBC接受到一条SQL语句时，会陆续执行SQL解析=&gt;查询优化=&gt;SQL路由=&gt;SQL改写=&gt;SQL执行=&gt;结果归并，最终返回执行结果</p><p>SQL解析过程分为<strong>词法解析和语法解析</strong>。 词法解析器用于将SQL拆解为不可再分的原子符号，称为Token。并根据不同数据库方言所提供的字典，将其归类为关键字，表达式，字面量和操作符。 再使用语法解析器将SQL转换为抽象语法树</p><p>例如，以下SQL：</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">SELECT</span> id<span class="token punctuation">,</span> name <span class="token keyword">FROM</span> t_user <span class="token keyword">WHERE</span> <span class="token keyword">status</span> <span class="token operator">=</span> <span class="token string">&#39;ACTIVE&#39;</span> <span class="token operator">AND</span> age <span class="token operator">&gt;</span> <span class="token number">18</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p><img src="https://gitee.com/LuisApai/apai_imags/raw/master/image-20220809144105250.png" alt="image-20220809144105250"></p><p>为了便于理解，抽象语法树中的关键字的Token用绿色表示，变量的Token用红色表示，灰色表示需要进一步拆分。</p><p>最后，通过对抽象语法树的遍历去提炼分片所需的<strong>上下文</strong>，并标记有可能需要<strong>SQL改写</strong>的位置。 供分片使用的解析上下文包含查询选择项（Select Items）、表信息（Table）、分片条件（Sharding Condition）、自增主键信息（Auto increment Primary Key）、排序信息（Order By）、分组信息（Group By）以及分页信息（Limit、Rownum、Top）</p><h2 id="sql路由" tabindex="-1"><a class="header-anchor" href="#sql路由" aria-hidden="true">#</a> SQL路由</h2><p>SQL路由就是把针对<strong>逻辑表</strong>的数据操作映射到对数据结点操作的过程。</p><p>根据解析上下文匹配数据库和表的分片策略，并生成路由路径。 对于携带分片键的SQL，根据分片键操作符不同可以划分为单片路由(分片键的操作符是等号)、多片路由(分片键的操作符是IN)和范围路由(分片键的操作符是BETWEEN)，不携带分片键的SQL则采用广播路由。根据分片键进行路由的场景可分为直接路由、标准路由、笛卡尔路由等</p><h3 id="标准路由" tabindex="-1"><a class="header-anchor" href="#标准路由" aria-hidden="true">#</a> 标准路由</h3><p>标准路由是Sharding-Jdbc最为推荐使用的分片方式，它的适用范围是不包含关联查询或仅包含绑定表之间关联查询的SQL。 当分片运算符是等于号时，路由结果将落入单库（表），当分片运算符是BETWEEN或IN时，则路由结果不一定落入唯一的库（表），因此一条逻辑SQL最终可能被拆分为多条用于执行的真实SQL。 举例说明，如果按order_id的奇数和偶数进行数据分片，一个单表查询的SQL如下</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t_order <span class="token keyword">WHERE</span> order_id <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>那么路由的结果为：</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t_order_0 <span class="token keyword">WHERE</span> order_id <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t_order_1 <span class="token keyword">WHERE</span> order_id <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>绑定表的关联查询与单表查询复杂度和性能相当。举例说明，如果一个包含绑定表的关联查询的SQL如下：</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t_order o <span class="token keyword">JOIN</span> t_order_item i <span class="token keyword">ON</span> o<span class="token punctuation">.</span>order_id<span class="token operator">=</span>i<span class="token punctuation">.</span>order_id <span class="token keyword">WHERE</span> order_id <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>那么路由的结果应为：</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t_order_0 o <span class="token keyword">JOIN</span> t_order_item_0 i <span class="token keyword">ON</span> o<span class="token punctuation">.</span>order_id<span class="token operator">=</span>i<span class="token punctuation">.</span>order_id <span class="token keyword">WHERE</span> order_id <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t_order_1 o <span class="token keyword">JOIN</span> t_order_item_1 i <span class="token keyword">ON</span> o<span class="token punctuation">.</span>order_id<span class="token operator">=</span>i<span class="token punctuation">.</span>order_id <span class="token keyword">WHERE</span> order_id <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><p>可以看到，SQL拆分的数目与单表是一致的</p><h4 id="笛卡尔路由" tabindex="-1"><a class="header-anchor" href="#笛卡尔路由" aria-hidden="true">#</a> 笛卡尔路由</h4><p>笛卡尔路由是最复杂的情况，它无法根据<strong>绑定表</strong>的关系定位分片规则，因此非绑定表之间的关联查询需要拆解为笛卡尔积组合执行。 如果上个示例中的SQL并未配置绑定表关系，那么路由的结果应为</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t_order_0 o <span class="token keyword">JOIN</span> t_order_item_0 i <span class="token keyword">ON</span> o<span class="token punctuation">.</span>order_id<span class="token operator">=</span>i<span class="token punctuation">.</span>order_id <span class="token keyword">WHERE</span> order_id <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t_order_0 o <span class="token keyword">JOIN</span> t_order_item_1 i <span class="token keyword">ON</span> o<span class="token punctuation">.</span>order_id<span class="token operator">=</span>i<span class="token punctuation">.</span>order_id <span class="token keyword">WHERE</span> order_id <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t_order_1 o <span class="token keyword">JOIN</span> t_order_item_0 i <span class="token keyword">ON</span> o<span class="token punctuation">.</span>order_id<span class="token operator">=</span>i<span class="token punctuation">.</span>order_id <span class="token keyword">WHERE</span> order_id <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t_order_1 o <span class="token keyword">JOIN</span> t_order_item_1 i <span class="token keyword">ON</span> o<span class="token punctuation">.</span>order_id<span class="token operator">=</span>i<span class="token punctuation">.</span>order_id <span class="token keyword">WHERE</span> order_id <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>笛卡尔路由查询性能较低，需谨慎使用</p><h3 id="全库表路由" tabindex="-1"><a class="header-anchor" href="#全库表路由" aria-hidden="true">#</a> 全库表路由</h3><p>对于不携带分片键的SQL，则采取广播路由的方式。根据SQL类型又可以划分为全库表路由、全库路由、全实例路由、单播路由和阻断路由这5种类型。其中全库表路由用于处理对数据库中与其逻辑表相关的所有真实表的操作， 主要包括不带分片键的DQL(数据查询)和DML（数据操纵），以及DDL（数据定义）等。例如</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t_order <span class="token keyword">WHERE</span> good_prority <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">#good_prority不是分片键</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>则会遍历所有数据库中的所有表，逐一匹配逻辑表和真实表名，能够匹配得上则执行。路由后成为</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t_order_0 <span class="token keyword">WHERE</span> good_prority <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">;</span> 
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t_order_1 <span class="token keyword">WHERE</span> good_prority <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">;</span> 
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t_order_2 <span class="token keyword">WHERE</span> good_prority <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">;</span> 
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t_order_3 <span class="token keyword">WHERE</span> good_prority <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="ql改写" tabindex="-1"><a class="header-anchor" href="#ql改写" aria-hidden="true">#</a> QL改写</h2><p>工程师面向<strong>逻辑表</strong>书写的SQL，并不能够直接在真实的数据库中执行，SQL改写用于将逻辑SQL改写为在真实数据库中可以正确执行的SQL。</p><p>如一个简单的例子，若逻辑SQL为：</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">SELECT</span> order_id <span class="token keyword">FROM</span> t_order <span class="token keyword">WHERE</span> order_id<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>假设该SQL配置分片键order_id，并且order_id=1的情况，将路由至分片表1。那么改写之后的SQL应该为：</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">SELECT</span> order_id <span class="token keyword">FROM</span> t_order_1 <span class="token keyword">WHERE</span> order_id<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>再比如，Sharding-JDBC需要在结果归并时获取相应数据，但该数据并未能通过查询的SQL返回。 这种情况主要是针对GROUP BY和ORDER BY。结果归并时，需要根据GROUP BY和ORDER BY的字段项进行分组和排序，但如果原始SQL的选择项中若并未包含分组项或排序项，则需要对原始SQL进行改写。 先看一下原始SQL中带有结果归并所需信息的场景：</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">SELECT</span> order_id<span class="token punctuation">,</span> user_id <span class="token keyword">FROM</span> t_order <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> user_id<span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>由于使用user_id进行排序，在结果归并中需要能够获取到user_id的数据，而上面的SQL是能够获取到user_id数据的，因此无需补列</p><p>如果选择项中不包含结果归并时所需的列，则需要进行补列，如以下SQL</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">SELECT</span> order_id <span class="token keyword">FROM</span> t_order <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> user_id<span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>由于原始SQL中并不包含需要在结果归并中需要获取的user_id，因此需要对SQL进行补列改写。补列之后的SQL 是：</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">SELECT</span> order_id<span class="token punctuation">,</span> user_id <span class="token keyword">AS</span> ORDER_BY_DERIVED_0 <span class="token keyword">FROM</span> t_order <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> user_id<span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><h2 id="sql执行" tabindex="-1"><a class="header-anchor" href="#sql执行" aria-hidden="true">#</a> SQL执行</h2><p>Sharding-JDBC采用一套自动化的执行引擎，负责将路由和改写完成之后的真实SQL安全且高效发送到底层数据源执行。 它不是简单地将SQL通过JDBC直接发送至数据源执行；也并非直接将执行请求放入线程池去并发执行。它更关注平衡数据源连接创建以及内存占用所产生的消耗，以及最大限度地合理利用并发等问题。 执行引擎的目标是自动化的平衡资源控制与执行效率，他能在以下两种模式自适应切换</p><h3 id="内存限制模式" tabindex="-1"><a class="header-anchor" href="#内存限制模式" aria-hidden="true">#</a> 内存限制模式</h3><p>使用此模式的前提是，Sharding-JDBC对一次操作所耗费的数据库连接数量不做限制。 如果实际执行的SQL需要对某数据库实例中的200张表做操作，则对每张表创建一个新的数据库连接，并通过多线程的方式并发处理，200个线程同时执行，以达成执行效率最大化</p><h3 id="连接限制模式" tabindex="-1"><a class="header-anchor" href="#连接限制模式" aria-hidden="true">#</a> 连接限制模式</h3><p>使用此模式的前提是，Sharding-JDBC严格控制对一次操作所耗费的数据库连接数量。 如果实际执行的SQL需要对某数据库实例中的200张表做操作，那么只会创建唯一的数据库连接，并对其200张表串行处理。 如果一次操作中的分片散落在不同的数据库，仍然采用多线程处理对不同库的操作，但每个库的每次操作仍然只创建一个唯一的数据库连接。</p><p>内存限制模式适用于OLAP操作，可以通过放宽对数据库连接的限制提升系统吞吐量； 连接限制模式适用于OLTP操作，OLTP通常带有分片键，会路由到单一的分片，因此严格控制数据库连接，以保证在线系统数据库资源能够被更多的应用所使用，是明智的选择</p><blockquote><p>OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，比如数据库记录的增删查改；实时性要求高，高并发OLAP是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。</p></blockquote><h2 id="结果归并" tabindex="-1"><a class="header-anchor" href="#结果归并" aria-hidden="true">#</a> 结果归并</h2><p>将从各个数据节点获取的多数据结果集，组合成为一个结果集并正确的返回至请求客户端，称为结果归并。</p><p>Sharding-JDBC支持的结果归并从功能上可分为遍历、排序、分组、分页、聚合5种类型，它们是组合而非互斥的关系。</p><p>结果归并从结构划分可分为流式归并、内存归并和装饰者归并，流式归并和内存归并是互斥的，装饰者归并可以在流式归并和内存归并之上做进一步的处理。</p><p><strong>内存归并</strong>很容易理解，他是将所有分片结果集的数据都遍历并存储在内存中，再通过统一的分组、排序以及聚合等 计算之后，再将其封装成为逐条访问的数据结果集返回</p><p><strong>流式归并</strong>是指每一次从数据库结果集中获取到的数据，都能够通过游标逐条获取的方式返回正确的单条数据，它与数据库原生的返回结果集的方式最为契合。</p><p>下边举例说明排序归并的过程，如下图是一个通过分数进行排序的示例图，它采用流式归并方式。 图中展示了3张表返回的数据结果集，每个数据结果集已经根据分数排序完毕，但是3个数据结果集之间是无序的。 将3个数据结果集的当前游标指向的数据值进行排序，并放入优先级队列，t_score_0的第一个数据值最大，t_score_2的第一个数据值次之，t_score_1的第一个数据值最小，因此优先级队列根据t_score_0，t_score_2和t_score_1的方式排序队列</p><p>下图则展现了进行next调用的时候，排序归并是如何进行的。 通过图中我们可以看到，当进行第一次next调用时，排在队列首位的t_score_0将会被弹出队列，并且将当前游标指向的数据值（也就是100）返回至查询客户端， 并且将游标下移一位之后，重新放入优先级队列。 而优先级队列也会根据t_score_0的当前数据结果集指向游标的数据值（这里是90）进行排序，根据当前数值，t_score_0排列在队列的最后一位。 之前队列中排名第二的t_score_2的数据结果集则自动排在了队列首位。</p><p>在进行第二次next时，只需要将目前排列在队列首位的t_score_2弹出队列，并且将其数据结果集游标指向的值返回至客户端，并下移游标，继续加入队列排队，以此类推。 当一个结果集中已经没有数据了，则无需再次加入队列</p><p>可以看到，对于每个数据结果集中的数据有序，而多数据结果集整体无序的情况下，Sharding-JDBC无需将所有的数据都加载至内存即可排序。 它使用的是流式归并的方式，每次next仅获取唯一正确的一条数据，极大的节省了内存的消耗。</p><p><strong>装饰者归并</strong>是对所有的结果集归并进行统一的功能增强，比如归并时需要聚合SUM前，在进行聚合计算前，都会通 过内存归并或流式归并查询出结果集。因此，聚合归并是在之前介绍的归并类型之上追加的归并能力，即装饰者模式</p></div></div><footer class="page-meta"><!----><div class="meta-item last-updated"><span class="xicon-container left meta-item-label"><!--[--><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 32 32" class="xicon-icon" style="width:20px;height:20px;font-size:20px;color:inherit;"><path d="M26 4h-4V2h-2v2h-8V2h-2v2H6c-1.1 0-2 .9-2 2v20c0 1.1.9 2 2 2h20c1.1 0 2-.9 2-2V6c0-1.1-.9-2-2-2zm0 22H6V12h20v14zm0-16H6V6h4v2h2V6h8v2h2V6h4v4z" fill="currentColor"></path></svg><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Last Updated 2023-07-19 09:58:39<!--]--></span></span></div></footer><nav class="page-nav"><p class="hasNext inner"><!----><span class="page-nav-item next">Swagger 接口测试 → </span></p></nav><!----></main><!--]--><div class="page-catalog-container"><h5 class="tip">文章层级目录</h5><ul><!--[--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#分库分表的方式" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="分库分表的方式"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->分库分表的方式<!--]--></span></span><!--[--><!--]--></a></li><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#垂直分表" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="垂直分表"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->垂直分表<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#垂直分库" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="垂直分库"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->垂直分库<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#水平分库" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="水平分库"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->水平分库<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#水平分表" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="水平分表"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->水平分表<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#分库分表小结" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="分库分表小结"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->分库分表小结<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#分库分表带来的问题" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="分库分表带来的问题"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->分库分表带来的问题<!--]--></span></span><!--[--><!--]--></a></li><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#事务一致性问题" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="事务一致性问题"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->事务一致性问题<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#跨节点访问" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="跨节点访问"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->跨节点访问<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#跨节点分页、排序函数" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="跨节点分页、排序函数"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->跨节点分页、排序函数<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#主键避重" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="主键避重"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->主键避重<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#公共表" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="公共表"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->公共表<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#引入maven依赖" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="引入maven依赖"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->引入maven依赖<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#分片规则配置" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="分片规则配置"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->分片规则配置<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#水平分库分表" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="水平分库分表"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->水平分库分表<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#垂直分库分表" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="垂直分库分表"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->垂直分库分表<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#公共表-1" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="公共表"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->公共表<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#_1-新增mysql的实例" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="1.新增mysql的实例"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->1.新增mysql的实例<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#_2-修改主、从库的配置文件-my-ini" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="2.修改主、从库的配置文件(my.ini)"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->2.修改主、从库的配置文件(my.ini)<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#_3-授权主从复制专用账号" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="3.授权主从复制专用账号"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->3.授权主从复制专用账号<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#_4-确认位点" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="4.确认位点"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->4.确认位点<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#_5-设置从库向主库同步数据检查链路" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="5.设置从库向主库同步数据检查链路"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->5.设置从库向主库同步数据检查链路<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#基本概念" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="基本概念"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->基本概念<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#sql解析" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="SQL解析"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->SQL解析<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#sql路由" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="SQL路由"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->SQL路由<!--]--></span></span><!--[--><!--]--></a></li><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#标准路由" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="标准路由"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->标准路由<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#全库表路由" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="全库表路由"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->全库表路由<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#ql改写" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="QL改写"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->QL改写<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#sql执行" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="SQL执行"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->SQL执行<!--]--></span></span><!--[--><!--]--></a></li><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#内存限制模式" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="内存限制模式"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->内存限制模式<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#连接限制模式" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="连接限制模式"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->连接限制模式<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Rests-docs/ShardingJDBC.html#结果归并" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="结果归并"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->结果归并<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--]--></ul></div></div></div></div><!----><!----><!--]--></div>
    <script type="module" src="/assets/app-8a43a0f0.js" defer></script>
  </body>
</html>
